Cover lesson · Maths · Year 8 · Intermediate
DCF: Data and computational thinking → Problem-solving and modelling · LNF: Numeracy → Calculation
Name: Class: Date:
What this practises: digital competence and numeracy in Maths. Pupils audit a printed spreadsheet model for a Year 8 charity fun run, spot four formula bugs (hardcoded values, missing brackets breaking order of operations, a relative reference that needed dollar signs, and an incomplete =SUM range), calculate the true totals by hand, and compare the mean and median when one runner raises an outlier of £145.
Board: open the supply teacher presentation for board slides, timers and script.
Print: this worksheet, one per pupil, and the stimulus sheet “Ysgol Cwm Branwen charity fun run spreadsheet printout”, one between two. Calculators are optional (all numbers work out to whole pounds).
Run: 5 min starter and read stimulus · 5 min key words · 10 min Step 1, audit the 4 formulas · 10 min Step 2, calculate true totals · 12 min Step 3, mean, median and outlier · 5 min Step 4, newsletter recommendation · 3 min plenary check. Total 50 minutes.
Collect: this worksheet. Quick checks: fixed formulas are E6: =(B6+C6)*D6, E7: =(B7+C7)*D7, F8: =D8*$B$2, G12: =SUM(G5:G11); corrected totals are Megan £35.00, Osian £35.00, Catrin £40.00, Grand Total £385.00; Mean = £55.00, Median = £40.00, Range = £115.00, Mean without Lowri = £40.00.
Seven Year 8 pupils at Ysgol Cwm Branwen ran around Llyn Brenig to raise money for the Wales Air Ambulance. When the organiser counted the cash and online pledges, there was £385.00, but cell G12 on the spreadsheet only shows £212.00. You will audit the spreadsheet formulas on the stimulus sheet, fix the 4 errors, calculate the true figures by hand, and decide whether the mean or the median is the fairer average to print in the school newsletter.
=).B2 shifts to B3 when dragged down one row; $B$2 locks both column B and row 2 so it never shifts.Compare the two tables on your stimulus sheet (the displayed numbers and the formula view). Find the 4 cells with broken formulas, explain what error was made, and write the corrected spreadsheet formula starting with =.
| Cell | Formula typed on printout | What is wrong with this formula? | Corrected formula |
|---|---|---|---|
| E6 (Megan) |
|||
| E7 (Osian) |
|||
| F8 (Catrin) |
|||
| G12 (Grand Total) |
(a) Work out the true values for the three rows that had broken row formulas, showing your calculation clearly.
| 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 | ||||
| Row 7: Osian | ||||
| Row 8: Catrin |
(b) Add all 7 pupils’ true Final Totals in Column G (Rows 5 to 11) to find the true Grand Total in cell G12, and work out how much money was missing from the printed Grand Total of £212.00.
True Grand Total (G12) working:
Difference between true Grand Total and printed £212.00:
(a) Write all 7 pupils’ true Final Totals (Column G) in order from smallest to largest:
(b) Complete the table of averages and spread below. Show your calculation in the middle column.
| Statistic | Calculation / method | Answer (£) |
|---|---|---|
| Mean of all 7 pupils | ||
| Median of all 7 pupils | ||
| Range of all 7 pupils | ||
| Mean of the 6 pupils without Lowri’s £145.00 outlier |
The head of year has written this draft sentence for the newsletter: “Our typical Year 8 runner raised £55.00 for the Wales Air Ambulance.” Write 3 or 4 sentences explaining why £55.00 is misleading, which average she should use instead, and how the spreadsheet can still celebrate Lowri’s achievement fairly.
$ signs and wrong cell ranges in a spreadsheet.= using cell references, brackets, $B$2 and =SUM.