Spreadsheets and ICT (Foundation)
Spreadsheets and ICT is the use of software such as Excel to enter, sort, calculate and display statistical data using built-in formulae, instead of calculating everything by hand. In the exam you may need to state which formula or feature would process a given data set, or interpret the result that a stated formula produces.
Before you start
Make sure you're comfortable with these topics first:
Method
- Enter each piece of raw data into its own cell, usually one column per variable.
- Choose the correct built-in function for the calculation needed, such as =AVERAGE(range) for the mean, =SUM(range) for a total, =COUNT(range) for how many values there are, or =MAX(range) and =MIN(range) for the largest and smallest values.
- Select the correct range of cells as the input to the formula, making sure not to include headings or unrelated data.
- Use sort and filter tools to order the data or isolate a subset that meets a condition before analysing it.
- Use the chart tool to turn a table of data into a bar chart, pie chart or line graph automatically.
- Check the output makes sense in context, since a formula applied to the wrong range gives a meaningless answer.
Common mistakes
MistakeIncluding a total row inside the range for another function, such as =MAX(B2:B10) when B10 holds the sum of B2:B9, so the maximum returned is the total.
InsteadSelect only the data cells, B2:B9, and keep totals outside the range you analyse. Sense-check the answer: a maximum that is bigger than every value you entered means a total or another extra cell is inside the range.
MistakeTyping a formula without the equals sign, such as MEDIAN(C2:C11), which the spreadsheet shows as text and does not calculate.
InsteadBegin every formula with =, then the function name, brackets and the range, for example =MEDIAN(C2:C11).
MistakeWriting a range with a dash, such as =SUM(D3-D12), which subtracts one cell from the other.
InsteadUse a colon for a range: =SUM(D3:D12). A colon means 'from D3 to D12'; a comma separates individual cells.
Worked examples
Example 1
The numbers of goals scored by a football team in 10 matches are entered into cells A1 to A10 of a spreadsheet: 2, 0, 1, 3, 1, 2, 4, 0, 1, 2. State the formula that would calculate the mean number of goals per match, and use it to find the mean.
- The formula for the mean of a range of cells is =AVERAGE(A1:A10).
- Add the 10 values to find the total: 2+0+1+3+1+2+4+0+1+2 = 16.
- Divide the total by the number of matches: 16/10 = 1.6.
- So the final answer is: formula =AVERAGE(A1:A10), giving a mean of 1.6 goals per match.
Example 2
The times, in seconds, taken by 8 runners are in cells B2 to B9 of a spreadsheet: 12.4, 11.8, 13.1, 12.0, 11.9, 12.6, 25.3, 12.2. (a) Write formulas for the mean and the median, and give the value each returns to 1 decimal place. (b) A student says the mean is the best value to describe a typical run. Use your answers to comment. (c) Write one formula that gives the mean time of the other seven runners, everyone except the slowest, and state the value it returns to 1 decimal place.
- (a) Mean: =AVERAGE(B2:B9). The total is 111.3, so the mean is 111.3 / 8 = 13.9 seconds (1 d.p.).
- Median: =MEDIAN(B2:B9). The ordered times are 11.8, 11.9, 12.0, 12.2, 12.4, 12.6, 13.1, 25.3, so the median is (12.2 + 12.4) / 2 = 12.3 seconds.
- (b) The mean, 13.9, is larger than seven of the eight times, because the one very slow time of 25.3 pulls it up. The median, 12.3, is closer to what most runners did, so the median describes a typical run better here.
- (c) Take the slowest time out of the total and out of the count: =(SUM(B2:B9)-MAX(B2:B9))/(COUNT(B2:B9)-1).
- The total without the slowest is 111.3 - 25.3 = 86.0 seconds for 7 runners, so the formula returns 86.0 / 7 = 12.3 seconds (1 d.p.), almost the same as the median.
- Final answer: (a) =AVERAGE(B2:B9) gives 13.9 and =MEDIAN(B2:B9) gives 12.3; (b) the median is the better description because one slow time distorts the mean; (c) =(SUM(B2:B9)-MAX(B2:B9))/(COUNT(B2:B9)-1) returns 12.3, which supports (b).
Example 3
A spreadsheet stores four fictional journey times in cells B2:B5: 12, 18, 24 and 42 minutes. A new journey time of 30 minutes is then entered in B6. A mean formula still reads =AVERAGE(B2:B5). Work out the mean returned by that formula and the mean using all five recorded journeys, then identify exactly which records the formula includes.
- The formula =AVERAGE(B2:B5) includes B2, B3, B4 and B5. Their total is 12 + 18 + 24 + 42 = 96 minutes, and 96 / 4 = 24 minutes.
- The new observation is in B6, which is outside the formula's range B2:B5, so the displayed formula omits the 30-minute journey.
- For all five records, add the new value: 96 + 30 = 126 minutes. Divide by 5: 126 / 5 = 25.2 minutes.
- Repair the range so the formula includes B6: =AVERAGE(B2:B6). That range now contains five cells, B2 through B6.
- Final answer: =AVERAGE(B2:B5) returns 24 minutes from the first four journeys, while =AVERAGE(B2:B6) returns 25.2 minutes from all five; the original formula omits the new observation in B6.
Practice questions
Q1[1 mark]Which spreadsheet formula would you use to find the total of the values in cells B2 to B20?Show answer
Answer: =SUM(B2:B20)
Q2[1 mark]Which spreadsheet formula would you use to find how many values are in the range C1 to C50?Show answer
Answer: =COUNT(C1:C50)
Q3[1 mark]A column of 8 test scores is in cells D2 to D9. Give the formula for the mean score.Show answer
Answer: =AVERAGE(D2:D9)
Q4[1 mark]Which formula finds the highest value in the range E1 to E30?Show answer
Answer: =MAX(E1:E30)
Q5[1 mark]A spreadsheet shows the formula =SUM(F1:F5)/COUNT(F1:F5) in cell F6. What statistical measure does this formula calculate?Show answer
Answer: The mean (the total of the values divided by how many values there are).
Q6[1 mark]Data on 200 students' exam scores is in a spreadsheet. Give one advantage of using a spreadsheet rather than calculating the mean and range by hand.Show answer
Answer: It is much faster and less prone to arithmetic errors, especially with a large data set (200 values).
Exam-style questions
Written in the style of a GCSE Statistics exam paper, with a full mark scheme.
Exam tips
- Sense-check every result against the data: a mean must lie between the MIN and the MAX of the values.
- Type each formula into an empty cell outside the range it uses. A formula typed inside its own range, such as =SUM(B2:B10) in B10, refers to itself, so the spreadsheet warns of a circular reference and does not give the total.
Q1[2 marks]The values 5, 8, 8, 12, 17 are entered into cells A1 to A5 of a spreadsheet. State the formula you would type into cell A6 to calculate the range of these values, and give the value it would return.Show mark scheme
Tick each line you got.
Nothing ticked yet - 2 available
Q2[3 marks]A spreadsheet contains the ages of 40 club members in cells B2 to B41. (a) Write a formula to count how many members are in the data set. (b) Write a formula to find the mean age. (c) The formula in (b) returns 34.5. State what this value represents.Show mark scheme
Tick each line you got.
Nothing ticked yet - 3 available
Q3[4 marks]A shop manager records the daily sales totals, in pounds, for 30 days in cells C2 to C31 of a spreadsheet. (a) Write a formula to calculate the total sales over the 30 days. (b) Write a formula to calculate the mean daily sales. (c) State a spreadsheet feature that could help her quickly find on how many days sales were above 500 pounds.Show mark scheme
Tick each line you got.
Nothing ticked yet - 4 available
See real GCSE Statistics past-paper questions, with official mark schemes
This topic in real past papers
We have tagged some boards' past papers question by question (not every board or year yet). This topic comes up in:
- Edexcel GCSE Statistics past papers: 2 questions across 2 papers, latest tagged June 2022
- AQA GCSE Statistics past papers: 1 question across 1 paper, latest tagged June 2019
Free printable worksheet
Want more practice on paper? Download the Spreadsheets and ICT (Foundation) worksheet pack - 38 pages of exam-style questions with a full mark scheme. Free to download, no card or account. Print it for personal and classroom use.
This topic is chapter 11 of GCSE Statistics Foundation Essentials 1, the whole course as one free printable PDF. Its worksheet is also chapter 3 of GCSE Statistics Foundation Topic Workbook 2 and chapter 11 of GCSE Statistics Foundation 10-Minute Tests 1, free printable practice books.
Other versions of this worksheet: