Worked example · Maths · Year 8 · Intermediate
DCF: Data and computational thinking → Problem-solving and modelling · LNF: Numeracy → Calculation
A complete model response and answer key for the cover teacher and for marking. Headline answers: E6 = =(B6+C6)*D6 (true E6 = £28.00, G6 = £35.00); E7 = =(B7+C7)*D7 (true E7 = £28.00, G7 = £35.00); F8 = =D8*$B$2 (true F8 = £8.00, G8 = £40.00); G12 = =SUM(G5:G11) (true Grand Total = £385.00, which is £173.00 higher than £212.00). Mean of all 7 = £55.00; Median = £40.00; Range = £115.00; Mean of the 6 without Lowri = £40.00.
| Cell | Formula typed on printout | What is wrong with this formula? | Corrected formula |
|---|---|---|---|
| E6 (Megan) |
=(2.50+1.50)*5 |
It uses hardcoded numbers instead of cell references. When Megan’s distance in D6 changed from 5 km to 7 km, E6 stayed at £20.00 instead of updating to £28.00. | =(B6+C6)*D6 |
| E7 (Osian) |
=B7+C7*D7 |
Missing brackets around B7+C7. Following BIDMAS, the spreadsheet multiplied C7*D7 (2.00 × 7 = 14.00) first and only added the £2.00 base rate once, giving £16.00 instead of £28.00. |
=(B7+C7)*D7 |
| F8 (Catrin) |
=D8*B3 |
A relative cell reference was dragged down without dollar signs, so B2 shifted to empty cell B3 (value 0), giving £0.00 instead of £8.00. |
=D8*$B$2 |
| G12 (Grand Total) |
=SUM(G5:G10) |
The cell range stops at Row 10 and leaves out Row 11 (Lowri’s £145.00). | =SUM(G5:G11) |
Identifies all four distinct spreadsheet errors (hardcoded literals, order of operations, relative vs absolute cell referencing, and range bounds) and writes exact corrected formulas starting with an equals sign. Evidences criteria 1 and 2.
| Row and pupil | Working out for Sponsor total (Col E) and PTA bonus (Col F) | True Col E (£) | True Col F (£) | True Final Total Col G (£) |
|---|---|---|---|---|
| Row 6: Megan | Col E: (£2.50 + £1.50) × 7 km = £4.00 × 7 = £28.00 Col F: 7 km × £1.00 = £7.00 |
28.00 | 7.00 | 35.00 |
| Row 7: Osian | Col E: (£2.00 + £2.00) × 7 km = £4.00 × 7 = £28.00 Col F: 7 km × £1.00 = £7.00 |
28.00 | 7.00 | 35.00 |
| Row 8: Catrin | Col E: (£3.00 + £1.00) × 8 km = £4.00 × 8 = £32.00 Col F: 8 km × £1.00 = £8.00 |
32.00 | 8.00 | 40.00 |
(b) True Grand Total (G12) working: £30.00 + £35.00 + £35.00 + £40.00 + £45.00 + £55.00 + £145.00 = £385.00.
Difference between true Grand Total and printed £212.00: £385.00 − £212.00 = £173.00 (£8.00 missing for Megan + £12.00 missing for Osian + £8.00 missing for Catrin + £145.00 missing for Lowri = £173.00).
Shows complete arithmetic working for each row and cross-checks that the four individual shortfalls add up to the £173.00 discrepancy. Evidences criterion 3.
(a) Ordered list of all 7 Final Totals: £30.00, £35.00, £35.00, £40.00, £45.00, £55.00, £145.00.
| Statistic | Calculation / method | Answer (£) |
|---|---|---|
| Mean of all 7 pupils | £385.00 ÷ 7 | 55.00 |
| Median of all 7 pupils | 4th value in the ordered list of 7 values | 40.00 |
| Range of all 7 pupils | £145.00 − £30.00 | 115.00 |
| Mean of the 6 pupils without Lowri’s £145.00 outlier | (£385.00 − £145.00) ÷ 6 = £240.00 ÷ 6 | 40.00 |
Orders the data before finding the median and shows the division clearly for both means. Evidences criterion 4.
Quoting the mean of £55.00 as a “typical” runner’s total is misleading because 5 out of the 7 runners actually raised less than £55.00, and the mean is pulled upwards by Lowri’s corporate sponsorship outlier of £145.00. Without Lowri’s outlier, the mean of the other six runners is £40.00, which matches the median of £40.00. The head of year should quote the median (£40.00) as the typical amount raised per pupil, while also celebrating the full Grand Total of £385.00 and thanking Lowri separately for her £145.00 contribution.
Uses the calculated figures to justify choosing the median over the mean and gives a practical way to report both typical performance and the outlier fairly. Evidences criterion 4.
=AVERAGE(G5:G11). Cell G14 (Median): =MEDIAN(G5:G11).$B$2, changing B2 updates all seven rows and cell G12 automatically. The 7 pupils ran 6 + 7 + 7 + 8 + 9 + 10 + 10 = 57 km altogether, so an extra £0.50 per km adds 57 × £0.50 = £28.50, giving a new Grand Total of £385.00 + £28.50 = £413.50.$, and range bounds).=.