Skip to the worksheet
Revision Library
Choose a subject

Spreadsheets and ICT (Higher) - Worksheets, Questions and Revision

53 original exam-style questions - 50 pages of questions with a full mark scheme - free printable PDF.

This topic is chapter 5 of GCSE Statistics Higher Topic Workbook 2 and chapter 12 of GCSE Statistics Higher 10-Minute Tests 1.

Revision Library
revisionlibrary.co.uk
HIGHER

2.4 Spreadsheets and ICT (Higher)

EDEXCEL 1ST0 · Calculator allowed · about 225 minutes
Total Marks
Name: _______________________________    Date: ____ / ____ / ______
Answer ALL questions. Show all your working.
1
A student writes the following formula into a spreadsheet cell:

=SUM(B2:B9)
(a)State what the equals sign (=) at the start of this entry tells the spreadsheet.(1)
(b)State what the word SUM tells the spreadsheet to do.(1)
(c)State what the part (B2:B9) tells the spreadsheet.(1)
(Total for Question 1 is 3 marks)
2
A gym keeps a record, in a spreadsheet, of how many times each of 6 members visited last month, and the total number of minutes they spent training.
ABC1234567NameVisits this monthTotal minutesZara8240Reuben5150Freya12300Isaac6210Layla9270Kofi4100
(a)Write down the value in cell B5.(1)
(b)Write down the cell reference of the cell that contains the value 300.(1)
(c)A formula, =C2/B2, is typed into cell D2. State, in the context of this spreadsheet, what this formula calculates.(1)
(d)Work out the value that would appear in cell D2.(1)
(Total for Question 2 is 4 marks)
3
For each statement about spreadsheets, write down whether it is True or False.
(i)The formula =MEDIAN(B2:B11) returns the mean of the 10 values in the range B2 to B11.(1)
(ii)A cell reference written with dollar signs, such as $B$2, is called an absolute reference, and does not change when the formula is copied to another cell.(1)
(Total for Question 3 is 2 marks)
4
A cinema keeps a record, in a spreadsheet, of the ticket price and the number of tickets sold for 3 films. Column D is used to calculate the total revenue for each film.
ABCD1234FilmTicket price (£)Tickets soldTotal revenue (£)Action Movie8.50120Comedy Show7.0095Animated Film6.50150
(a)Write down the formula that should be typed into cell D2 to calculate the total revenue for Action Movie.(1)
(b)Work out the value that would appear in cell D2.(2)
(c)The manager notices that the ticket price for Action Movie was entered incorrectly, and corrects cell B2 from 8.50 to 9.00. State what happens to the value shown in cell D2 as a result, and explain why this happens without the manager needing to retype the formula in D2.(2)
(Total for Question 4 is 5 marks)
5
A courier records the time, in minutes, taken for each of 7 deliveries in a spreadsheet, as shown below. She wants cell B9 to show the mean delivery time, and cell B10 to show the median delivery time.
1A DeliveryB Time (minutes)2Delivery 1183Delivery 2224Delivery 3155Delivery 4306Delivery 5217Delivery 6198Delivery 7259Mean10Median
(a)Write a spreadsheet formula, using a named function, that could be typed into cell B9 to calculate the mean of the seven delivery times in B2 to B8.(2)
(b)Work out the value that would appear in cell B9, giving your answer correct to 1 decimal place.(2)
(c)Write a spreadsheet formula, using a named function, that could be typed into cell B10 to calculate the median of the seven delivery times.(1)
(d)Work out the value that would appear in cell B10.(1)
(Total for Question 5 is 6 marks)
6
A warehouse stock-take spreadsheet has 500 rows of data, one for each product, in rows 2 to 501. Cell D2 contains the formula =B2-C2, which finds the discrepancy between the counted stock and the recorded stock for the first product. This formula is then copied down to cell D501 using the fill handle.
ABC1234...250...501Product codeCounted stockRecorded stockP0014850P002120118P0037672.........P2496565.........P5003033
(a)Give one advantage of being able to copy the formula in cell D2 down to all 500 rows using the fill handle in a single action, rather than typing 500 separate formulas by hand.(1)
(b)Given that the formula in cell D2 is =B2-C2, using only relative cell references, write down the formula that would appear in cell D250 after it has been copied down using the fill handle.(2)
(Total for Question 6 is 3 marks)
7
A coach records the category and race time, in seconds, of 8 athletes in a spreadsheet, as shown below.
ABC123456789NameCategoryTime (s)AliU1513.8BeaU1713.5CaiU1714.2DeeU1512.9EmiU1713.9FayU1514.5GusU1713.2HanaU1513.6
(a)The data is sorted first by Category (A to Z), and then, within each category, by Time (smallest to largest). Write down the name that would appear in row 2 after this two-level sort.(1)
(b)Write down the name that would appear in row 6 after this two-level sort.(1)
(c)A filter is then applied to the original (unsorted) data, so that only athletes in category U17 with a time under 13.8 seconds remain visible. Write down the names that would remain visible.(2)
(Total for Question 7 is 4 marks)
8
A quality-control inspector weighs 6 packets from a production line and enters the weights, in grams, into a spreadsheet, as shown below. Cell B8 contains the formula =AVERAGE(B2:B7).
1A PacketB Weight (g)C Squared deviation2Packet 11983Packet 22024Packet 31965Packet 42046Packet 52007Packet 62008Mean9Standard deviation
(a)The formula =(B2-$B$8)2 is typed into cell C2, and copied down to cells C3 to C7. Explain why the reference to cell B8 must be an absolute reference ($B$8), rather than a relative reference (B8), for this formula to work correctly when it is copied down.(2)
(b)Work out the value that would appear in cell B8.(2)
(c)Work out the value that would appear in cell C4.(2)
(d)A further cell contains the formula =SQRT(AVERAGE(C2:C7)). State what statistical measure this formula calculates for the six packet weights.(1)
(Total for Question 8 is 7 marks)
9
A teacher enters the scores, out of 100, that 7 students achieved in a test into a spreadsheet, as shown below. The pass mark is 40. The formula =IF(B2≥40,"Pass","Fail") is typed into cell C2, and copied down to cell C8.
1A StudentB ScoreC Pass or Fail2Tariq553Grace384Oscar405Nadia626Callum297Ruby458Simone40
(a)Write down the value that would appear in cell C3.(1)
(b)Write down the value that would appear in cell C4.(1)
(c)Write a formula, using the COUNTIF function, that would count how many of the 7 students scored 40 or more (that is, how many passed).(2)
(d)Work out the value this formula would return.(1)
(Total for Question 9 is 5 marks)
10
A spreadsheet's chart tool is used to plot a scatter graph of hours of revision (x) against test score out of 100 (y) for 12 students, and to add a trendline automatically.
(a)State the type of correlation you would expect between hours of revision and test score.(1)
(b)Give one advantage of using the spreadsheet's chart tool to add a trendline automatically, rather than drawing a line of best fit by eye.(1)
(c)The trendline has equation y = 4.5x + 32, where x is hours of revision and y is predicted test score. Use this equation to predict the test score for a student who revises for 6 hours.(1)
(d)The same equation is used to predict the score for a student who plans to revise for 20 hours. Work out this prediction, and comment on whether it is reliable.(2)
(Total for Question 10 is 5 marks)
11
A sports scientist records the reaction time, in milliseconds, of 40 sprinters in cells B2 to B41 of a spreadsheet. She groups the results into 4 class intervals and uses spreadsheet formulas to count how many reaction times fall into each class.
Reaction time, t (ms)Frequency
150 ≤ t < 1708
170 ≤ t < 190?
190 ≤ t < 21010
210 ≤ t < 2307
(a)Write a formula, using the COUNTIFS function, that would count how many of the 40 reaction times are at least 170 ms but less than 190 ms, referencing the data in cells B2 to B41.(2)
(b)Given that the total number of reaction times recorded is 40, use the table to work out the missing frequency for the class 170 ≤ t < 190.(2)
(Total for Question 11 is 4 marks)
12
A national weather agency processes the daily rainfall total recorded at each of 3000 weather stations, using spreadsheet software rather than manual calculation.
(a)Give one advantage of using spreadsheet software, rather than manual calculation, to find the mean daily rainfall across all 3000 weather stations.(1)
(b)Give one disadvantage or risk of relying entirely on spreadsheet software when processing data on this scale.(1)
(c)"For very large data sets, using ICT is always better than manual calculation." Discuss this statement, giving a reason for the statement and a reason against it, and give a justified overall conclusion.(3)
(Total for Question 12 is 5 marks)
13
A shop assistant's spreadsheet is meant to calculate the mean weekly footfall (number of customers) from 9 weeks of data stored in cells B2 to B10. The formula used is =SUM(B2:B9)/8.
(a)Identify the error in this formula.(1)
(b)Write a corrected formula, using a named function, that would correctly calculate the mean footfall for all 9 weeks in B2 to B10.(2)
(c)The original (incorrect) formula, =SUM(B2:B9)/8, returns a value of 46.75. Use this to work out the sum of the footfall values in cells B2 to B9, and explain whether it is possible to determine the correct mean of all 9 weeks (B2 to B10) using only this information.(2)
(Total for Question 13 is 5 marks)
14
A university research team plans to investigate wellbeing among 15 000 students nationally, using ICT at each stage of the statistical enquiry cycle: plan, collect, process and interpret.
(a)Give one way a spreadsheet's random number function could be used at the plan stage, before the main survey, to help select a manageable sample from the full list of 15 000 students.(2)
(b)Give one limitation of collecting responses using an online survey alone at the collect stage, for this population of students.(2)
(c)State one way ICT allows this large data set to be processed that would not be feasible by hand within a reasonable time.(1)
(d)A researcher claims: "Because the spreadsheet performed all the calculations, the results must be reliable." Evaluate this claim.(2)
(Total for Question 14 is 7 marks)
15
A small local sports club has 40 members, and wants to work out the total and mean annual subscription income. A national retailer processes about 2 million transactions every month, and wants to work out the same kind of summary statistics.

Compare these two situations, and recommend, with justification, whether each organisation should rely mainly on manual calculation or on ICT/database software to process its data.
(Total for Question 15 is 4 marks)
16
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Lemons57
3: Pears1110
4: Grapes206
5: Apples185
6: Cherries1614

Work out the value produced by each formula.
(a) =MAX(C2:C6)
(b) =SUM(C2:C6)
(c) =MAX(B2:B6)-MIN(B2:B6)
(Total for Question 16 is 3 marks)
17
The table shows information about the number of books read in a month for 35 students.
Books readFrequency
05
18
28
31
412
51

(a) Work out the median.
(b) Work out the mean. Give your answer to 2 decimal places if necessary.
(c) Work out the range.
(Total for Question 17 is 6 marks)
18
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Week 1C: Week 2
2: Limes53
3: Grapes1410
4: Melons143
5: Lemons132
6: Pears410

Work out the value produced by each formula.
(a) =COUNTIF(B2:B6,">7")
(b) =MAX(C2:C6)
(c) =B6*C6
(Total for Question 18 is 3 marks)
19
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Week 1C: Week 2
2: Melons82
3: Apples83
4: Cherries206
5: Pears96
6: Grapes1913

Work out the value produced by each formula.
(a) =MAX(C2:C6)
(b) =B3+C3
(c) =SUM(C2:C6)
(Total for Question 19 is 3 marks)
20
The table shows information about the number of pets owned for 32 families surveyed.
Number of petsFrequency
07
15
27
38
45

(a) Work out the median.
(b) Work out the mean. Give your answer to 2 decimal places if necessary.
(c) Work out the range.
(Total for Question 20 is 6 marks)
21
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Cherries1315
3: Apples63
4: Lemons410
5: Limes610
6: Melons199

Work out the value produced by each formula.
(a) =B6*C6
(b) =SUM(C2:C6)
(c) =AVERAGE(B2:B6)
(Total for Question 21 is 3 marks)
22
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Week 1C: Week 2
2: Apples186
3: Pears1012
4: Grapes1911
5: Cherries33
6: Lemons1211

Work out the value produced by each formula.
(a) =MAX(B2:B6)-MIN(B2:B6)
(b) =B3+C3
(c) =B2*C2
(Total for Question 22 is 3 marks)
23
The table shows information about the number of eggs in a nest for 45 nests inspected.
EggsFrequency
012
16
23
35
48
511

(a) Write down the mode.
(b) Work out the median.
(c) Work out the mean. Give your answer to 2 decimal places if necessary.
(Total for Question 23 is 6 marks)
24
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Cherries103
3: Melons114
4: Grapes192
5: Plums119
6: Pears29

Work out the value produced by each formula.
(a) =MAX(C2:C6)
(b) =B6*C6
(c) =B3+C3
(Total for Question 24 is 3 marks)
25
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Apples96
3: Grapes1212
4: Lemons189
5: Melons108
6: Limes156

Work out the value produced by each formula.
(a) =COUNTIF(B2:B6,">7")
(b) =B3+C3
(c) =AVERAGE(B2:B6)
(Total for Question 25 is 3 marks)
26
The table shows information about the number of rooms in a house for 33 houses on a street.
RoomsFrequency
112
26
39
45
51

(a) Work out the sum of the frequencies, and so confirm the number of items.
(b) Work out the median.
(c) Work out the mean. Give your answer to 2 decimal places if necessary.
(Total for Question 26 is 6 marks)
27
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Lemons124
3: Limes1415
4: Cherries1814
5: Grapes1410
6: Pears146

Work out the value produced by each formula.
(a) =B3+C3
(b) =AVERAGE(B2:B6)
(c) =MAX(C2:C6)
(Total for Question 27 is 3 marks)
28
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Week 1C: Week 2
2: Pears1715
3: Apples26
4: Limes105
5: Grapes122
6: Melons46

Work out the value produced by each formula.
(a) =MAX(C2:C6)
(b) =SUM(B2:B6)
(c) =MIN(C2:C6)
(Total for Question 28 is 3 marks)
29
The table shows information about the number of eggs in a nest for 29 nests inspected.
EggsFrequency
07
15
28
36
43

(a) Work out the sum of the frequencies, and so confirm the number of items.
(b) Write down the mode.
(c) Work out the mean. Give your answer to 2 decimal places if necessary.
(Total for Question 29 is 5 marks)
30
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Plums72
3: Lemons199
4: Apples29
5: Melons1912
6: Pears1410

Work out the value produced by each formula.
(a) =MIN(C2:C6)
(b) =COUNTIF(B2:B6,">9")
(c) =B6*C6
(Total for Question 30 is 3 marks)
31
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Cherries49
3: Pears167
4: Lemons44
5: Apples142
6: Melons85

Work out the value produced by each formula.
(a) =AVERAGE(B2:B6)
(b) =B3+C3
(c) =SUM(B2:B6)
(Total for Question 31 is 3 marks)
32
The table shows information about the number of goals scored in a match for 28 matches.
GoalsFrequency
08
12
22
34
412

(a) Work out the median.
(b) Work out the mean. Give your answer to 2 decimal places if necessary.
(c) Work out the range.
(Total for Question 32 is 6 marks)
33
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Week 1C: Week 2
2: Melons1010
3: Limes711
4: Plums83
5: Lemons1611
6: Apples108

Work out the value produced by each formula.
(a) =SUM(C2:C6)
(b) =AVERAGE(B2:B6)
(c) =MAX(C2:C6)
(Total for Question 33 is 3 marks)
34
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Cherries714
3: Apples1714
4: Pears1313
5: Limes176
6: Plums1614

Work out the value produced by each formula.
(a) =COUNTIF(B2:B6,">9")
(b) =AVERAGE(B2:B6)
(c) =B2*C2
(Total for Question 34 is 3 marks)
35
The table shows information about the number of days absent in a term for 49 pupils.
Days absentFrequency
012
110
27
39
411

(a) Work out the median.
(b) Work out the mean. Give your answer to 2 decimal places if necessary.
(c) Work out the range.
(Total for Question 35 is 6 marks)
36
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Lemons1215
3: Cherries213
4: Pears183
5: Grapes53
6: Melons1213

Work out the value produced by each formula.
(a) =B2*C2
(b) =COUNTIF(B2:B6,">12")
(c) =MAX(C2:C6)
(Total for Question 36 is 3 marks)
37
The table shows information about the number of cars owned by a household for 17 households.
Number of carsFrequency
19
21
33
43
51

(a) Work out the sum of the frequencies, and so confirm the number of items.
(b) Write down the mode.
(c) Work out the mean. Give your answer to 2 decimal places if necessary.
(Total for Question 37 is 5 marks)
38
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Pears188
3: Lemons95
4: Limes1513
5: Melons315
6: Cherries107

Work out the value produced by each formula.
(a) =AVERAGE(B2:B6)
(b) =B6*C6
(c) =MIN(C2:C6)
(Total for Question 38 is 3 marks)
39
The table shows information about the number of books read in a month for 35 students.
Books readFrequency
02
16
29
33
45
510

(a) Write down the mode.
(b) Work out the median.
(c) Work out the mean. Give your answer to 2 decimal places if necessary.
(Total for Question 39 is 6 marks)
40
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Limes188
3: Melons202
4: Apples88
5: Lemons98
6: Pears188

Work out the value produced by each formula.
(a) =B6*C6
(b) =SUM(B2:B6)
(c) =AVERAGE(B2:B6)
(Total for Question 40 is 3 marks)
41
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Week 1C: Week 2
2: Lemons82
3: Apples1415
4: Melons37
5: Limes27
6: Cherries29

Work out the value produced by each formula.
(a) =MIN(C2:C6)
(b) =B6*C6
(c) =COUNTIF(B2:B6,">7")
(Total for Question 41 is 3 marks)
42
The table shows information about the number of eggs in a nest for 35 nests inspected.
EggsFrequency
14
24
38
410
59

(a) Work out the sum of the frequencies, and so confirm the number of items.
(b) Write down the mode.
(c) Work out the mean. Give your answer to 2 decimal places if necessary.
(Total for Question 42 is 5 marks)
43
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Apples153
3: Melons136
4: Plums152
5: Limes209
6: Pears128

Work out the value produced by each formula.
(a) =MAX(B2:B6)-MIN(B2:B6)
(b) =B6*C6
(c) =COUNTIF(B2:B6,">11")
(Total for Question 43 is 3 marks)
44
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Lemons215
3: Plums413
4: Cherries1615
5: Pears1312
6: Apples1410

Work out the value produced by each formula.
(a) =COUNTIF(B2:B6,">8")
(b) =SUM(C2:C6)
(c) =B6*C6
(Total for Question 44 is 3 marks)
45
The table shows information about the number of cars owned by a household for 28 households.
Number of carsFrequency
05
13
23
36
411

(a) Work out the sum of the frequencies, and so confirm the number of items.
(b) Write down the mode.
(c) Work out the mean. Give your answer to 2 decimal places if necessary.
(Total for Question 45 is 5 marks)
46
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Plums128
3: Limes69
4: Cherries87
5: Apples124
6: Grapes214

Work out the value produced by each formula.
(a) =MAX(B2:B6)-MIN(B2:B6)
(b) =B6*C6
(c) =AVERAGE(B2:B6)
(Total for Question 46 is 3 marks)
47
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Lemons1615
3: Plums167
4: Limes153
5: Apples1011
6: Cherries144

Work out the value produced by each formula.
(a) =MIN(C2:C6)
(b) =COUNTIF(B2:B6,">7")
(c) =SUM(C2:C6)
(Total for Question 47 is 3 marks)
48
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Lemons183
3: Apples145
4: Limes157
5: Melons1415
6: Plums1514

Work out the value produced by each formula.
(a) =B2*C2
(b) =B6*C6
(c) =SUM(B2:B6)
(Total for Question 48 is 3 marks)
49
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: HoursC: Rate
2: Plums179
3: Apples615
4: Limes1014
5: Grapes199
6: Pears1014

Work out the value produced by each formula.
(a) =AVERAGE(B2:B6)
(b) =SUM(B2:B6)
(c) =MIN(C2:C6)
(Total for Question 49 is 3 marks)
50
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Week 1C: Week 2
2: Pears1915
3: Grapes204
4: Cherries32
5: Lemons614
6: Melons107

Work out the value produced by each formula.
(a) =COUNTIF(B2:B6,">6")
(b) =B6*C6
(c) =MIN(C2:C6)
(Total for Question 50 is 3 marks)
51
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Plums104
3: Cherries814
4: Grapes185
5: Melons1011
6: Limes1412

Work out the value produced by each formula.
(a) =MIN(C2:C6)
(b) =SUM(C2:C6)
(c) =MAX(B2:B6)-MIN(B2:B6)
(Total for Question 51 is 3 marks)
52
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Mark 1C: Mark 2
2: Pears118
3: Apples118
4: Grapes75
5: Melons173
6: Cherries1613

Work out the value produced by each formula.
(a) =B2*C2
(b) =MAX(C2:C6)
(c) =MIN(C2:C6)
(Total for Question 52 is 3 marks)
53
A spreadsheet has these entries. Column A holds the labels, column B and column C hold the numbers, and row 1 is the heading row (not shown).
Row (A)B: Price (pounds)C: Quantity
2: Cherries88
3: Lemons112
4: Plums208
5: Pears1615
6: Limes54

Work out the value produced by each formula.
(a) =COUNTIF(B2:B6,">11")
(b) =SUM(B2:B6)
(c) =MIN(C2:C6)
(Total for Question 53 is 3 marks)
Mark scheme · 2.4 Spreadsheets and ICT (Higher)

Question 1

  • (a) shows the spreadsheet that the entry is a formula (a calculation), not plain text or a numberB1
  • (a) Answer: It shows the spreadsheet that this is a formula (a calculation), not plain text or a number typed into the cell.
  • (b) add together (find the total of) the values in the given range of cellsB1
  • (b) Answer: It tells the spreadsheet to add together (find the total of) the values in the range that follows.
  • (c) the range of cells to use in the calculation, from cell B2 to cell B9 inclusiveB1
  • (c) Answer: It tells the spreadsheet which range of cells to use in the calculation, from cell B2 to cell B9 inclusive.

Question 2

  • (a) 6 caoB1
  • (a) Answer: 6
  • (b) C4 caoB1
  • (b) Answer: C4
  • (c) the average (mean) number of minutes Zara spent per visitB1
  • (c) Answer: The average (mean) number of minutes Zara spent per visit.
  • (d) 30 caoB1
  • (d) Answer: 30

Question 3

  • (i) False caoB1
  • (i) Answer: False
  • (ii) True caoB1
  • (ii) Answer: True

Question 4

  • (a) =B2*C2 oe (accept =C2*B2)B1
  • (a) Answer: =B2*C2
  • (b) 8.50 x 120, oeM1
  • (b) 1020 or 1020.00 caoA1
  • (b) Answer: 1020.00
  • (c) the value in D2 updates/recalculates automatically (to 1080)B1
  • (c) because the formula in D2 references cell B2, and a spreadsheet automatically recalculates any formula whenever a value it depends on changesB1
  • (c) Answer: It updates automatically to 1080, because the formula in D2 refers to cell B2, and a spreadsheet recalculates a formula automatically whenever a value it references changes.

Question 5

  • (a) AVERAGE used as a named function, e.g. =AVERAGE(...)B1
  • (a) correct range B2:B8B1
  • (a) Answer: =AVERAGE(B2:B8)
  • (b) (18+22+15+30+21+19+25) / 7, oeM1
  • (b) 21.4 awrtA1
  • (b) Answer: 21.4
  • (c) =MEDIAN(B2:B8) oeB1
  • (c) Answer: =MEDIAN(B2:B8)
  • (d) 21 caoB1
  • (d) Answer: 21

Question 6

  • (a) any valid advantage, e.g. it is much faster, oe it avoids the risk of mistyping one of the 500 individual formulasB1
  • (a) Answer: It is much faster, and it avoids the risk of mistyping one of the 500 formulas individually.
  • (b) recognises that both relative references shift down by the same number of rows as the copyB1
  • (b) =B250-C250 caoB1
  • (b) Answer: =B250-C250

Question 7

  • (a) Dee caoB1
  • (a) Answer: Dee
  • (b) Gus caoB1
  • (b) Answer: Gus
  • (c) Bea and Gus identified (13.5 and 13.2, both category U17 and under 13.8 seconds)B1
  • (c) Cai and Emi correctly excluded (14.2 and 13.9 are not under 13.8 seconds), with all category U15 athletes also excludedB1
  • (c) Answer: Bea, Gus

Question 8

  • (a) without the $ signs, the reference to B8 would shift down by one row for each row copied (e.g. to B9, then B10, and so on)B1
  • (a) the mean is only stored once, in cell B8, so every copy of the formula must refer back to that same fixed cell to calculate each deviation correctlyB1
  • (a) Answer: Without the dollar signs, the reference would shift down a row each time the formula is copied (to B9, then B10, and so on), but the mean is only stored in cell B8, so every copy must refer back to that one fixed cell for the calculation to work.
  • (b) (198+202+196+204+200+200) / 6, oeM1
  • (b) 200 caoA1
  • (b) Answer: 200
  • (c) (196-200)2, oe (using the value 196 from B4 and the mean 200 from B8)M1
  • (c) 16 caoA1
  • (c) Answer: 16
  • (d) the standard deviation caoB1
  • (d) Answer: The standard deviation

Question 9

  • (a) Fail caoB1
  • (a) Answer: Fail
  • (b) Pass caoB1
  • (b) Answer: Pass
  • (c) COUNTIF used as a named function with a correct condition, e.g. =COUNTIF(...,"≥40")B1
  • (c) correct range B2:B8B1
  • (c) Answer: =COUNTIF(B2:B8,"≥40")
  • (d) 5 caoB1
  • (d) Answer: 5

Question 10

  • (a) positive (correlation) caoB1
  • (a) Answer: Positive correlation
  • (b) any valid advantage, e.g. it is calculated by a fixed, consistent method, so it does not vary between people or between attempts, unlike a line drawn by eyeB1
  • (b) Answer: It is calculated by a fixed method, so it gives the same precise line every time, unlike a line of best fit drawn by eye, which can vary from person to person.
  • (c) 59 caoB1
  • (c) Answer: 59
  • (d) 4.5 x 20 + 32, oe (= 122)M1
  • (d) not reliable, e.g. 20 hours is far beyond the range of the original data (extrapolation), and 122 is not even a possible test score out of 100B1
  • (d) Answer: 122; this is not reliable, since 20 hours is far beyond the range of the original data (extrapolation), and a score of 122 is not even possible out of 100.

Question 11

  • (a) COUNTIFS used as a named function with two correct conditions, e.g. =COUNTIFS(...,"≥170",...,"<190")B1
  • (a) correct range B2:B41 used for both conditionsB1
  • (a) Answer: =COUNTIFS(B2:B41,"≥170",B2:B41,"<190")
  • (b) 40 - 8 - 10 - 7, oeM1
  • (b) 15 caoA1
  • (b) Answer: 15

Question 12

  • (a) any valid advantage, e.g. it can process a very large amount of data almost instantly, which would take a very long time by hand and would be far more likely to contain an arithmetic errorB1
  • (a) Answer: It processes all 3000 values almost instantly, which would take a very long time by hand and would be far more likely to contain an arithmetic error.
  • (b) any valid disadvantage, e.g. a single error in the data or in a formula could go unnoticed and affect every calculated result, or specialist software/training may be neededB1
  • (b) Answer: If the data or a formula contains even one error, it could affect every result calculated from it, and this might not be noticed among so many values.
  • (c) a reason supporting the statement, e.g. ICT can handle far more data, far more quickly and consistently, than a person calculating by handB1
  • (c) a reason against the statement, e.g. ICT still depends on the data and formulas being correct in the first place, and specialist software or training is not always availableB1
  • (c) a clear, justified conclusion, e.g. ICT is usually better for very large data sets, but is not automatically 'always' better, since the result is still only as good as the data and formulas usedB1
  • (c) Answer: ICT lets huge amounts of data be processed quickly and consistently, which manual calculation cannot match, but it is only as good as the data and formulas entered, so it is not automatically better in every case. Overall, ICT is usually the better choice for very large data sets, provided the data and formulas are checked.

Question 13

  • (a) the range only covers B2:B9 (8 cells), missing B10, and the formula divides by 8, when it should divide by 9 (for 9 values)B1
  • (a) Answer: The range should be B2:B10, to include all 9 weeks, and the formula should divide by 9, not 8.
  • (b) AVERAGE used as a named function, e.g. =AVERAGE(...)B1
  • (b) correct range B2:B10B1
  • (b) Answer: =AVERAGE(B2:B10)
  • (c) 46.75 x 8 = 374, so the sum of the values in B2:B9 is 374B1
  • (c) no, it is not possible, since the value in B10 (the 9th week) is not known, so the total for all 9 weeks cannot be found from this information aloneB1
  • (c) Answer: The sum of B2:B9 is 374 (46.75 x 8), but the value in B10 is still unknown, so the correct mean of all 9 weeks cannot be determined from this information alone.

Question 14

  • (a) names a function such as RAND or RANDBETWEENB1
  • (a) explains that this gives each student on the list a fair, unbiased chance of being chosen for the sampleB1
  • (a) Answer: A function such as RANDBETWEEN could assign each of the 15 000 students a random number, and the students with, say, the lowest 500 numbers are chosen, giving everyone a fair chance of selection.
  • (b) identifies that some students may not have reliable internet/email access, or may not check or respond to itB1
  • (b) explains that this could bias the sample, as certain groups of students may be under-represented in the resultsB1
  • (b) Answer: Some students may not have reliable internet access, or may simply not respond, so those students are under-represented, which could bias the results.
  • (c) any valid use, e.g. a spreadsheet function instantly calculates a summary statistic, such as the mean or standard deviation, for all 15 000 responsesB1
  • (c) Answer: A spreadsheet function can calculate the mean or standard deviation of all 15 000 responses in seconds.
  • (d) recognises that the reliability of the results depends on the quality of the data (and the sampling method), not only on the calculations performedB1
  • (d) explains that a spreadsheet will process incorrect, biased or poorly collected data without warning, so correct calculations alone do not guarantee reliable resultsB1
  • (d) Answer: This claim is not necessarily true. A spreadsheet only guarantees that the calculations are carried out correctly; if the data collected is inaccurate, incomplete, or comes from a biased sample, the spreadsheet will process it without warning, so the results could still be unreliable.

Question 15

  • the club's data set (40 members) is small enough that manual calculation is a reasonable, proportionate option, without the cost or training needed for specialist softwareB1
  • the retailer's data set (about 2 million transactions) is far too large for manual calculation to be practical or accurate within a reasonable time, so ICT/database software is essentialB1
  • a relevant risk/limitation is identified for at least one case, e.g. a single formula error in the retailer's software could affect an enormous number of records, so results still need to be checkedB1
  • a clear overall conclusion, e.g. the best method depends on the scale (and nature) of the data, rather than one method always being betterB1
  • Answer: For the club's 40 members, manual calculation is perfectly proportionate and avoids unnecessary software or training costs. For the retailer's roughly 2 million monthly transactions, manual calculation is not feasible in a reasonable time, so ICT/database software is essential, although any formula error could then affect a huge number of records, so results should still be checked. Overall, the best method depends on the scale of the data, rather than one method always being better.

Question 16

  • 14 caoB1
  • 42 caoB1
  • 15 caoB1
  • Answer: (a) 14; (b) 42; (c) 15

Question 17

  • identifies the position of the median (35 items, cumulative frequencies used)M1
  • 2 caoA1
  • products of value and frequency formed (at least 3 correct)M1
  • sum of products (80) divided by 35M1
  • 2.29 caoA1
  • 5 caoB1
  • Answer: (a) 2; (b) 2.29; (c) 5

Question 18

  • 3 caoB1
  • 10 caoB1
  • 40 caoB1
  • Answer: (a) 3; (b) 10; (c) 40

Question 19

  • 13 caoB1
  • 11 caoB1
  • 30 caoB1
  • Answer: (a) 13; (b) 11; (c) 30

Question 20

  • identifies the position of the median (32 items, cumulative frequencies used)M1
  • 2 caoA1
  • products of value and frequency formed (at least 3 correct)M1
  • sum of products (63) divided by 32M1
  • 1.97 caoA1
  • 4 caoB1
  • Answer: (a) 2; (b) 1.97; (c) 4

Question 21

  • 171 caoB1
  • 47 caoB1
  • 9.6 caoB1
  • Answer: (a) 171; (b) 47; (c) 9.6

Question 22

  • 16 caoB1
  • 22 caoB1
  • 108 caoB1
  • Answer: (a) 16; (b) 22; (c) 108

Question 23

  • 0 caoB1
  • identifies the position of the median (45 items, cumulative frequencies used)M1
  • 3 caoA1
  • products of value and frequency formed (at least 3 correct)M1
  • sum of products (114) divided by 45M1
  • 2.53 caoA1
  • Answer: (a) 0; (b) 3; (c) 2.53

Question 24

  • 9 caoB1
  • 18 caoB1
  • 15 caoB1
  • Answer: (a) 9; (b) 18; (c) 15

Question 25

  • 5 caoB1
  • 24 caoB1
  • 12.8 caoB1
  • Answer: (a) 5; (b) 24; (c) 12.8

Question 26

  • 33 caoB1
  • identifies the position of the median (33 items, cumulative frequencies used)M1
  • 2 caoA1
  • products of value and frequency formed (at least 3 correct)M1
  • sum of products (76) divided by 33M1
  • 2.30 caoA1
  • Answer: (a) 33; (b) 2; (c) 2.30

Question 27

  • 29 caoB1
  • 14.4 caoB1
  • 15 caoB1
  • Answer: (a) 29; (b) 14.4; (c) 15

Question 28

  • 15 caoB1
  • 45 caoB1
  • 2 caoB1
  • Answer: (a) 15; (b) 45; (c) 2

Question 29

  • 29 caoB1
  • 2 caoB1
  • products of value and frequency formed (at least 3 correct)M1
  • sum of products (51) divided by 29M1
  • 1.76 caoA1
  • Answer: (a) 29; (b) 2; (c) 1.76

Question 30

  • 2 caoB1
  • 3 caoB1
  • 140 caoB1
  • Answer: (a) 2; (b) 3; (c) 140

Question 31

  • 9.2 caoB1
  • 23 caoB1
  • 46 caoB1
  • Answer: (a) 9.2; (b) 23; (c) 46

Question 32

  • identifies the position of the median (28 items, cumulative frequencies used)M1
  • 3 caoA1
  • products of value and frequency formed (at least 3 correct)M1
  • sum of products (66) divided by 28M1
  • 2.36 caoA1
  • 4 caoB1
  • Answer: (a) 3; (b) 2.36; (c) 4

Question 33

  • 43 caoB1
  • 10.2 caoB1
  • 11 caoB1
  • Answer: (a) 43; (b) 10.2; (c) 11

Question 34

  • 4 caoB1
  • 14 caoB1
  • 98 caoB1
  • Answer: (a) 4; (b) 14; (c) 98

Question 35

  • identifies the position of the median (49 items, cumulative frequencies used)M1
  • 2 caoA1
  • products of value and frequency formed (at least 3 correct)M1
  • sum of products (95) divided by 49M1
  • 1.94 caoA1
  • 4 caoB1
  • Answer: (a) 2; (b) 1.94; (c) 4

Question 36

  • 180 caoB1
  • 1 caoB1
  • 15 caoB1
  • Answer: (a) 180; (b) 1; (c) 15

Question 37

  • 17 caoB1
  • 1 caoB1
  • products of value and frequency formed (at least 3 correct)M1
  • sum of products (37) divided by 17M1
  • 2.18 caoA1
  • Answer: (a) 17; (b) 1; (c) 2.18

Question 38

  • 11 caoB1
  • 70 caoB1
  • 5 caoB1
  • Answer: (a) 11; (b) 70; (c) 5

Question 39

  • 5 caoB1
  • identifies the position of the median (35 items, cumulative frequencies used)M1
  • 3 caoA1
  • products of value and frequency formed (at least 3 correct)M1
  • sum of products (103) divided by 35M1
  • 2.94 caoA1
  • Answer: (a) 5; (b) 3; (c) 2.94

Question 40

  • 144 caoB1
  • 73 caoB1
  • 14.6 caoB1
  • Answer: (a) 144; (b) 73; (c) 14.6

Question 41

  • 2 caoB1
  • 18 caoB1
  • 2 caoB1
  • Answer: (a) 2; (b) 18; (c) 2

Question 42

  • 35 caoB1
  • 4 caoB1
  • products of value and frequency formed (at least 3 correct)M1
  • sum of products (121) divided by 35M1
  • 3.46 caoA1
  • Answer: (a) 35; (b) 4; (c) 3.46

Question 43

  • 8 caoB1
  • 96 caoB1
  • 5 caoB1
  • Answer: (a) 8; (b) 96; (c) 5

Question 44

  • 3 caoB1
  • 65 caoB1
  • 140 caoB1
  • Answer: (a) 3; (b) 65; (c) 140

Question 45

  • 28 caoB1
  • 4 caoB1
  • products of value and frequency formed (at least 3 correct)M1
  • sum of products (71) divided by 28M1
  • 2.54 caoA1
  • Answer: (a) 28; (b) 4; (c) 2.54

Question 46

  • 10 caoB1
  • 28 caoB1
  • 8 caoB1
  • Answer: (a) 10; (b) 28; (c) 8

Question 47

  • 3 caoB1
  • 5 caoB1
  • 40 caoB1
  • Answer: (a) 3; (b) 5; (c) 40

Question 48

  • 54 caoB1
  • 210 caoB1
  • 76 caoB1
  • Answer: (a) 54; (b) 210; (c) 76

Question 49

  • 12.4 caoB1
  • 62 caoB1
  • 9 caoB1
  • Answer: (a) 12.4; (b) 62; (c) 9

Question 50

  • 3 caoB1
  • 70 caoB1
  • 2 caoB1
  • Answer: (a) 3; (b) 70; (c) 2

Question 51

  • 4 caoB1
  • 46 caoB1
  • 10 caoB1
  • Answer: (a) 4; (b) 46; (c) 10

Question 52

  • 88 caoB1
  • 13 caoB1
  • 3 caoB1
  • Answer: (a) 88; (b) 13; (c) 3

Question 53

  • 2 caoB1
  • 60 caoB1
  • 2 caoB1
  • Answer: (a) 2; (b) 60; (c) 2

Mark your answers

Check answers without an account. Some answers can be checked automatically; use the mark scheme to mark the rest.

Question 1

3 marks
Did your answer earn the marks?

Question 2

4 marks
Did your answer earn the marks?

Question 3

2 marks
Did your answer earn the marks?

Question 4

5 marks
Did your answer earn the marks?

Question 5

6 marks
Did your answer earn the marks?

Question 6

3 marks
Did your answer earn the marks?

Question 7

4 marks
Did your answer earn the marks?

Question 8

7 marks
Did your answer earn the marks?

Question 9

5 marks
Did your answer earn the marks?

Question 10

5 marks
Did your answer earn the marks?

Question 11

4 marks
Did your answer earn the marks?

Question 12

5 marks
Did your answer earn the marks?

Question 13

5 marks
Did your answer earn the marks?

Question 14

7 marks
Did your answer earn the marks?

Question 15

4 marks
Did your answer earn the marks?

Question 16

3 marks
Did your answer earn the marks?

Question 17

6 marks
Did your answer earn the marks?

Question 18

3 marks
Did your answer earn the marks?

Question 19

3 marks
Did your answer earn the marks?

Question 20

6 marks
Did your answer earn the marks?

Question 21

3 marks
Did your answer earn the marks?

Question 22

3 marks
Did your answer earn the marks?

Question 23

6 marks
Did your answer earn the marks?

Question 24

3 marks
Did your answer earn the marks?

Question 25

3 marks
Did your answer earn the marks?

Question 26

6 marks
Did your answer earn the marks?

Question 27

3 marks
Did your answer earn the marks?

Question 28

3 marks
Did your answer earn the marks?

Question 29

5 marks
Did your answer earn the marks?

Question 30

3 marks
Did your answer earn the marks?

Question 31

3 marks
Did your answer earn the marks?

Question 32

6 marks
Did your answer earn the marks?

Question 33

3 marks
Did your answer earn the marks?

Question 34

3 marks
Did your answer earn the marks?

Question 35

6 marks
Did your answer earn the marks?

Question 36

3 marks
Did your answer earn the marks?

Question 37

5 marks
Did your answer earn the marks?

Question 38

3 marks
Did your answer earn the marks?

Question 39

6 marks
Did your answer earn the marks?

Question 40

3 marks
Did your answer earn the marks?

Question 41

3 marks
Did your answer earn the marks?

Question 42

5 marks
Did your answer earn the marks?

Question 43

3 marks
Did your answer earn the marks?

Question 44

3 marks
Did your answer earn the marks?

Question 45

5 marks
Did your answer earn the marks?

Question 46

3 marks
Did your answer earn the marks?

Question 47

3 marks
Did your answer earn the marks?

Question 48

3 marks
Did your answer earn the marks?

Question 49

3 marks
Did your answer earn the marks?

Question 50

3 marks
Did your answer earn the marks?

Question 51

3 marks
Did your answer earn the marks?

Question 52

3 marks
Did your answer earn the marks?

Question 53

3 marks
Did your answer earn the marks?
Mark my answers

Worksheets are written for Revision Library and come with a full mark scheme; a few English packs quote an out-of-copyright text, and name it on the page. Exam board names and specification codes say which qualification a pack suits: we are not affiliated with, endorsed by or approved by any exam board, and no past-paper question is reproduced here. Rights queries: hello@revisionlibrary.co.uk