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.
(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.
(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.
(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.
(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.
(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).
(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.
(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 < 170
8
170 ≤ t < 190
?
190 ≤ t < 210
10
210 ≤ t < 230
7
(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)
Mark scheme · H10 Spreadsheets and ICT
Question 1
(a) B1 shows the spreadsheet that the entry is a formula (a calculation), not plain text or a number
(a) Answer: It shows the spreadsheet that this is a formula (a calculation), not plain text or a number typed into the cell.
(b) B1 add together (find the total of) the values in the given range of cells
(b) Answer: It tells the spreadsheet to add together (find the total of) the values in the range that follows.
(c) B1 the range of cells to use in the calculation, from cell B2 to cell B9 inclusive
(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) B1 6 cao
(a) Answer: 6
(b) B1 C4 cao
(b) Answer: C4
(c) B1 the average (mean) number of minutes Zara spent per visit
(c) Answer: The average (mean) number of minutes Zara spent per visit.
(d) B1 30 cao
(d) Answer: 30
Question 3
(i) B1 False cao
(i) Answer: False
(ii) B1 True cao
(ii) Answer: True
Question 4
(a) B1 =B2*C2 oe (accept =C2*B2)
(a) Answer: =B2*C2
(b) M1 8.50 x 120, oe
(b) A1 1020 or 1020.00 cao
(b) Answer: 1020.00
(c) B1 the value in D2 updates/recalculates automatically (to 1080)
(c) B1 because the formula in D2 references cell B2, and a spreadsheet automatically recalculates any formula whenever a value it depends on changes
(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) B1 AVERAGE used as a named function, e.g. =AVERAGE(...)
(a) B1 correct range B2:B8
(a) Answer: =AVERAGE(B2:B8)
(b) M1 (18+22+15+30+21+19+25) / 7, oe
(b) A1 21.4 awrt
(b) Answer: 21.4
(c) B1 =MEDIAN(B2:B8) oe
(c) Answer: =MEDIAN(B2:B8)
(d) B1 21 cao
(d) Answer: 21
Question 6
(a) B1 any valid advantage, e.g. it is much faster, oe it avoids the risk of mistyping one of the 500 individual formulas
(a) Answer: It is much faster, and it avoids the risk of mistyping one of the 500 formulas individually.
(b) B1 recognises that both relative references shift down by the same number of rows as the copy
(b) B1 =B250-C250 cao
(b) Answer: =B250-C250
Question 7
(a) B1 Dee cao
(a) Answer: Dee
(b) B1 Gus cao
(b) Answer: Gus
(c) B1 Bea and Gus identified (13.5 and 13.2, both category U17 and under 13.8 seconds)
(c) B1 Cai and Emi correctly excluded (14.2 and 13.9 are not under 13.8 seconds), with all category U15 athletes also excluded
(c) Answer: Bea, Gus
Question 8
(a) B1 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)
(a) B1 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 correctly
(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) M1 (198+202+196+204+200+200) / 6, oe
(b) A1 200 cao
(b) Answer: 200
(c) M1 (196-200)2, oe (using the value 196 from B4 and the mean 200 from B8)
(c) A1 16 cao
(c) Answer: 16
(d) B1 the standard deviation cao
(d) Answer: The standard deviation
Question 9
(a) B1 Fail cao
(a) Answer: Fail
(b) B1 Pass cao
(b) Answer: Pass
(c) B1 COUNTIF used as a named function with a correct condition, e.g. =COUNTIF(...,"≥40")
(c) B1 correct range B2:B8
(c) Answer: =COUNTIF(B2:B8,"≥40")
(d) B1 5 cao
(d) Answer: 5
Question 10
(a) B1 positive (correlation) cao
(a) Answer: Positive correlation
(b) B1 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 eye
(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) B1 59 cao
(c) Answer: 59
(d) M1 4.5 x 20 + 32, oe (= 122)
(d) B1 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 100
(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) B1 COUNTIFS used as a named function with two correct conditions, e.g. =COUNTIFS(...,"≥170",...,"<190")
(a) B1 correct range B2:B41 used for both conditions
(a) B1 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 error
(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) B1 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 needed
(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) B1 a reason supporting the statement, e.g. ICT can handle far more data, far more quickly and consistently, than a person calculating by hand
(c) B1 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 available
(c) B1 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 used
(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) B1 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)
(a) Answer: The range should be B2:B10, to include all 9 weeks, and the formula should divide by 9, not 8.
(b) B1 AVERAGE used as a named function, e.g. =AVERAGE(...)
(b) B1 correct range B2:B10
(b) Answer: =AVERAGE(B2:B10)
(c) B1 46.75 x 8 = 374, so the sum of the values in B2:B9 is 374
(c) B1 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 alone
(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) B1 names a function such as RAND or RANDBETWEEN
(a) B1 explains that this gives each student on the list a fair, unbiased chance of being chosen for the sample
(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) B1 identifies that some students may not have reliable internet/email access, or may not check or respond to it
(b) B1 explains that this could bias the sample, as certain groups of students may be under-represented in the results
(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) B1 any valid use, e.g. a spreadsheet function instantly calculates a summary statistic, such as the mean or standard deviation, for all 15 000 responses
(c) Answer: A spreadsheet function can calculate the mean or standard deviation of all 15 000 responses in seconds.
(d) B1 recognises that the reliability of the results depends on the quality of the data (and the sampling method), not only on the calculations performed
(d) B1 explains that a spreadsheet will process incorrect, biased or poorly collected data without warning, so correct calculations alone do not guarantee reliable results
(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
B1 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 software
B1 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 essential
B1 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 checked
B1 a clear overall conclusion, e.g. the best method depends on the scale (and nature) of the data, rather than one method always being better
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.