Slide 1 of 7 · Settling starter · Food and Nutrition · Year 6
5 minutesPlanning a Water Tracker Spreadsheet
Class 6 at Ysgol Penrhos counted the cups of water they drank at school for one week. Today you plan their spreadsheet on paper and decide what the numbers really show.
While the register is called: write your name, class and date on your worksheet, then read sections 1 and 2 of the stimulus sheet, the challenge and the paper log.
Starter question: How much does one school cup hold, and which drinks were not recorded?
Slide 2 of 7 · Today’s brief and key words · Food and Nutrition · Year 6
4 minutesYour brief: build the spreadsheet, then test what it tells you
You will enter the log for 6 pupils, write formulae for totals and means, fix 5 broken formulae, then decide which conclusions the data really supports.
Cell
One box in the grid, named by its column letter and row number, for example C4.
Formula
An instruction that always starts with =, such as =SUM(B2:F2), which adds up B2 to F2.
Mean
Add up the numbers, then divide by how many there are. =AVERAGE(B2:F2) divides by 5.
Slide 3 of 7 · Steps 1 and 2: enter the data and write the formulae · Food and Nutrition · Year 6
18 minutesSteps 1 and 2: copy the log in, then write the formulae
Step 1 (8 min): copy the paper log into the grid. Leave columns G and H and row 8 empty. Step 2 (12 min): write the 4 formulae, then fill in every empty cell in G, H and row 8.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Pupil | Mon | Tue | Wed | Thu | Fri | Total | Mean |
| 2 | Ffion | 2 | 3 | 2 | 4 | 4 | =SUM(B2:F2) → 15 |
=AVERAGE(B2:F2) → 3 |
Slide 4 of 7 · Step 3: debug the formulae · Food and Nutrition · Year 6
8 minutesStep 3: find the broken formulae and fix them
Another group typed 5 formulae. Tick the right ones. For each wrong one, say what it would show and write the correct formula.
G4 typed: =SUM(B4:E4) → No. It stops at Thursday, so it shows 16, not Amira’s total of 20. Correct: =SUM(B4:F4).
Ask yourself: Does the range cover all 5 days? Is it the right function? Does it start with =?
Slide 5 of 7 · Steps 4 and 5: read the results and test the conclusions · Food and Nutrition · Year 6
16 minutesSteps 4 and 5: what does the data really show?
Step 4: answer (a) to (e) using your totals, means and the notes from the week. Step 5: decide if each statement is true, false or can’t tell, and give a reason.
Step 4(a) model: Friday, with 20 cups. It was the hottest day (22 °C) and there was a sports afternoon on the field.
Step 5(a) model: “Every pupil drank more on Friday than on Monday” is False: Dylan went from 1 to 0, and Amira (4) and Leah (2) stayed the same.
Slide 6 of 7 · Self and partner check · Food and Nutrition · Year 6
3 minutesCheck your work against the success criteria
Swap worksheets with a partner or check your own. Tick each one only when you can point to it on the sheet.
- I can organise data in a spreadsheet using cell references.
- I can write SUM, AVERAGE, MAX and MIN formulae, starting with
=. - I can find and fix mistakes in formulae.
- I can decide whether a conclusion is supported, and spot when data could be misleading.
Slide 7 of 7 · Plenary review · Food and Nutrition · Year 6
3 minutesQuick-check answers before handing in
Check these three answers before your worksheets are collected.
| Question | Correct answer |
|---|---|
| What is the grand total in G8, and what is the mean of the 6 totals? | 85 cups; 85 ÷ 6 = 14.2 to 1 decimal place. |
Why does =MAX(G2:G7) not tell you who drank the most? |
It shows 20, but Amira and Tomos both have 20. MAX gives the number, not the name. |
| “Dylan does not drink enough each day.” True, false or can’t tell? | Can’t tell: the log only counts water at school, and Dylan left at 10:00 on Friday. |