Revision Library
Choose a subject
Menu
GCSE Statistics · Topic guide

Spreadsheets and ICT (Higher)

Spreadsheets and ICT is the use of computer software, such as a spreadsheet program, to enter, store and process statistical data, applying built-in functions to calculate summary statistics and produce charts automatically. In GCSE Statistics this means knowing formulas such as AVERAGE and MEDIAN, and explaining the advantages and limitations of using ICT compared with calculating by hand.

HigherProcessing and Representing DataEdexcelAQACCEA

Before you start

Make sure you're comfortable with these topics first:

Method

  1. Identify which built-in function calculates the statistic needed, for example AVERAGE for the mean, MEDIAN for the median, STDEV.P for the standard deviation (STDEV divides by n - 1, not n, so it gives a different value), or COUNTIF for a frequency.
  2. Enter the raw data into a single, continuous range of cells (a column or row) so a formula can reference the whole range at once.
  3. Use a sorting tool, or a function that does this automatically, before finding a median or quartiles by hand.
  4. Choose an appropriate chart type from the software's chart tool to match the data, for example a bar chart for categorical data or a scatter graph for bivariate data.
  5. When asked to discuss ICT, give a genuine advantage (speed, accuracy, automatic recalculation, easy to produce charts) or disadvantage (cost of software or training, data entry errors go unnoticed, no human judgement) rather than a vague statement.
  6. Estimate the expected size of the answer before trusting the software's output, so a data entry or formula mistake is easy to spot.

Common mistakes

  • MistakeTyping units into data cells, such as '12 kg', so the spreadsheet treats them as text.

    InsteadPut only numbers in the data cells and give the unit in the column heading. A function such as AVERAGE skips text cells in a range, so the answer may quietly use fewer values than you meant.

  • MistakeSorting only one column of paired data before finding a statistic, so the rows no longer match up.

    InsteadSort the whole table so that every row moves together, or leave the data unsorted. Functions such as MEDIAN work out the order themselves, so sorting is needed only to read the ordered list.

  • MistakeChoosing a chart by how it looks, for example a bar chart to show how two numerical variables are related.

    InsteadMatch the chart to the data: a bar chart for categories, and a scatter graph for pairs of numerical values, where each row gives one point.

Worked examples

Example 1

A spreadsheet contains the exam marks of 30 students in cells B2 to B31. State the spreadsheet formula that would calculate the mean mark, and give one advantage of using a spreadsheet rather than a calculator for this task.

  1. The mean is calculated with the AVERAGE function, applied to the whole range of data.
  2. Formula: =AVERAGE(B2:B31).
  3. Advantage: a spreadsheet processes all 30 values instantly and automatically recalculates the mean if any mark is corrected or added, reducing the chance of an arithmetic error.
  4. Final answer: =AVERAGE(B2:B31); advantage - faster and less error-prone than adding 30 values by hand, and it updates automatically if the data changes.

Example 2

A student records the rainfall, in mm, on 10 days in cells C2 to C11: 3.2, 4.1, 2.8, 3.6, 4.4, 35.0, 3.9, 2.7, 4.2, 3.3. The formula =AVERAGE(C2:C11) returns 6.72. (a) Use the data to estimate what the mean should be, and explain why the result is suspicious. (b) Find the value most likely to be a data-entry error and say what to do. (c) The record says that day's rainfall was 3.5 mm. Correct the entry, then give the mean and the median and say which changed more.

  1. (a) Nine of the ten values lie between 2.7 and 4.4, so the mean should be somewhere between about 3 and 4. A mean of 6.72 is larger than nine of the ten values, which suggests that one value is far larger than the rest or has been entered wrongly.
  2. (b) Scanning the column, 35.0 is about ten times the others and looks like 3.5 typed with the decimal point in the wrong place. Check it against the original record rather than guessing.
  3. (c) The record says 3.5 mm, so type 3.5 into cell C7, the sixth value. The formulas recalculate on their own.
  4. The total is now 67.2 - 35.0 + 3.5 = 35.7, so =AVERAGE(C2:C11) gives 3.57 mm, which fits the estimate.
  5. =MEDIAN(C2:C11) changes from 3.75 mm to 3.55 mm. The mean moved by more than 3 mm while the median moved by 0.2 mm, because one extreme value affects the mean much more.
  6. Final answer: the entry 35.0 should be 3.5 mm. After correcting it, the mean is 3.57 mm and the median is 3.55 mm, and the error had affected the mean far more than the median.

Practice questions

Q1[1 mark]Which spreadsheet function would you use to find the mean of the values in cells A1:A20?Show answer

Answer: =AVERAGE(A1:A20)

Got it right?
Your answerWork it out on paper, then show the answer and mark yourself.
Q2[1 mark]Which function counts how many values in the range B2:B50 are equal to 'Yes'?Show answer

Answer: =COUNTIF(B2:B50,"Yes")

Got it right?
Your answerWork it out on paper, then show the answer and mark yourself.
Q3[1 mark]A spreadsheet has 40 pieces of data in cells C2:C41. Give the formula to find the median.Show answer

Answer: =MEDIAN(C2:C41)

Got it right?
Your answerWork it out on paper, then show the answer and mark yourself.
Q4[1 mark]State one advantage of entering raw data into a spreadsheet rather than calculating statistics by hand.Show answer

Answer: It is much faster and less likely to contain arithmetic errors (accept any valid advantage, e.g. formulas update automatically)

Got it right?
Your answerWork it out on paper, then show the answer and mark yourself.
Q5[1 mark]State one disadvantage of relying on ICT and spreadsheets to process statistical data.Show answer

Answer: Errors made when entering the raw data can easily go unnoticed, since the formulas will still calculate an answer (accept any valid disadvantage, e.g. training or software cost)

Got it right?
Your answerWork it out on paper, then show the answer and mark yourself.
Q6[1 mark]A spreadsheet has the heights, in cm, of 25 plants in cells D2:D26. Give the formula for the range of heights.Show answer

Answer: =MAX(D2:D26)-MIN(D2:D26)

Got it right?
Your answerWork it out on paper, then show the answer and mark yourself.

Exam-style questions

Written in the style of a GCSE Statistics exam paper, with a full mark scheme.

Exam tips

  • Write the full formula: start with =, then the function name, the range and the closing bracket, for example =AVERAGE(B2:B9). A function name alone does not say which data it works on.
  • When asked to discuss using ICT, tie each point to the situation in the question rather than giving a general remark such as 'it is easier'.
Q1[2 marks]A spreadsheet contains the delivery times, in minutes, of 60 parcels in cells B2 to B61. Write down (a) the spreadsheet function used to find the mean delivery time and (b) the spreadsheet function used to find the standard deviation of the delivery times, treating the 60 parcels as the whole data set (divide by n).Show mark scheme

Tick each line you got.

Nothing ticked yet - 2 available

Got it right?
Q2[3 marks]Give three reasons why a company might prefer to use a spreadsheet, rather than manual calculation, to process the results of a large customer satisfaction survey.Show mark scheme

Tick each line you got.

Nothing ticked yet - 3 available

Got it right?
Q3[4 marks]A spreadsheet contains the number of hours 15 employees worked overtime in one week, in cells C2:C16. The formula =AVERAGE(C2:C16) returns 6.4 and the formula =MEDIAN(C2:C16) returns 5. (a) Explain what these two values suggest about the shape of the distribution of overtime hours. (b) State one advantage of using a spreadsheet to find these values rather than calculating them by hand.Show mark scheme

Tick each line you got.

Nothing ticked yet - 4 available

Got it right?

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:

Free printable worksheet

Want more practice on paper? Download the Spreadsheets and ICT (Higher) worksheet pack - 50 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 12 of GCSE Statistics Higher Essentials 1, the whole course as one free printable PDF. Its worksheet is also chapter 5 of GCSE Statistics Higher Topic Workbook 2 and chapter 12 of GCSE Statistics Higher 10-Minute Tests 1, free printable practice books.

Other versions of this worksheet:

Keep going

Not quite what you needed?

Tell us what is missing on Spreadsheets and ICT (Higher), or which topic to write up next.

Build a full practice pack.