Slide 1 of 7 · Settling starter · Maths · Year 8
5 minutesAuditing Formulas in a Charity Fun Run Spreadsheet
Year 8 at Ysgol Cwm Branwen have completed a charity fun run around Llyn Brenig. The spreadsheet printout shows a grand total of £212.00, but the cash tin holds £385.00.
While the register is called: write your name, class and date on your worksheet, then look at Row 7 (Osian) on the stimulus sheet.
Starter question: Osian gets £2.00 per km base rate plus £2.00 per km ridge bonus, and he ran 7 km. How much sponsor money should he have in cell E7, and why did the formula =B7+C7*D7 give £16.00 instead?
Slide 2 of 7 · Today’s brief and key words · Maths · Year 8
5 minutesYour job today: audit and fix the model
A spreadsheet is only as reliable as its formulas. Today you will spot 4 formula bugs on the printout, work out the true totals by hand, and compare the mean and median sponsorship.
Cell references
Relative (B2): changes row when dragged down.
Absolute ($B$2): locks cell B2 so it never shifts.
Order of operations
Spreadsheets calculate brackets first, then multiplication, then addition: use =(B5+C5)*D5.
Mean vs median
Mean: total divided by count.
Median: middle number when ordered.
Slide 3 of 7 · Step 1 · Spot and fix the 4 broken formulas
10 minutesStep 1: Audit the 4 broken cells
Check the formula view on your stimulus sheet against the four rules in section 2. Complete the Step 1 table on your worksheet.
| Cell | What was typed | What kind of bug is it? | Correct formula |
|---|---|---|---|
| E6 (Megan) | =(2.50+1.50)*5 |
Hardcoded numbers (used old 5 km distance instead of cell references) | =(B6+C6)*D6 |
| E7, F8, G12 | See stimulus sheet | Identify the bug and explain what went wrong | Write on your sheet |
Slide 4 of 7 · Step 2 · Recalculate the true totals
10 minutesStep 2: Calculate the true values by hand
Show your working for each corrected cell and find the true Grand Total in cell G12. Remember that cell B2 holds the PTA match bonus of £1.00 per kilometre.
Rule for Column E (Sponsor total): (Base rate + Ridge bonus) × Distance
Rule for Column F (PTA bonus): Distance × £1.00
Rule for Column G (Final pupil total): Column E + Column F
Slide 5 of 7 · Step 3 · Mean, median and the £145 outlier
12 minutesStep 3: Compare the mean and the median
Lowri raised £145.00 because her aunt’s company sponsored her £12.00 per kilometre. In Step 3, work out the mean, median and range of all 7 corrected totals, and the mean of the other 6 pupils without Lowri.
| Measure | How to calculate it |
|---|---|
| Mean (all 7 pupils) | Total raised (£385) ÷ 7 pupils |
| Median (all 7 pupils) | Put the 7 totals in order from smallest to largest and pick the 4th value |
| Mean without Lowri (6 pupils) | (£385 − £145) ÷ 6 pupils |
Slide 6 of 7 · Step 4 · Audit note and self-check
5 minutesStep 4: Write your recommendation for the newsletter
The head of year wants to write in the school newsletter what a “typical” runner raised. Complete Step 4 by explaining whether she should quote the mean (£55) or the median (£40), and why.
Check your work against the success criteria:
1. Did you start every spreadsheet formula with = and include $B$2 in F8?
2. Did you quote both the mean (£55) and the median (£40) and explain the effect of the £145 outlier?
Slide 7 of 7 · Plenary · Final answer check
3 minutesPlenary: Check your headline answers
Check your answers against the summary below before handing in your worksheet.
| Question | Correct answer |
|---|---|
| 4 fixed formulas | E6: =(B6+C6)*D6 · E7: =(B7+C7)*D7 · F8: =D8*$B$2 · G12: =SUM(G5:G11) |
| Corrected pupil totals | Megan G6 = £35.00 · Osian G7 = £35.00 · Catrin G8 = £40.00 |
| True Grand Total & averages | G12 = £385.00 · Mean = £55.00 · Median = £40.00 · Range = £115.00 |