Worked example · Health and Well-being · Year 6

Planning a water tracker spreadsheet

DCF: Data and computational thinking → Data and information literacy

Also assessed, LNF: Numeracy → Learning that statistics represent data and that probability models chance helps us make informed inferences and decisions → Interpreting data

What this is

A secure Year 6 response, annotated so you can see which success criterion each part meets. It is also the answer key. Key figures: totals 15, 5, 20, 15, 10, 20; means 3, 1, 4, 3, 2, 4; day totals 15, 16, 16, 18, 20; grand total 85; mean of the totals 14.2; 4 pupils above it; Amira 4 litres. Step 5: (a) false, (b) can’t tell, (c) can’t tell, (d) true.

Steps 1 and 2: the finished spreadsheet

ABCDEFGH
1PupilMonTueWedThuFriTotalMean
2Ffion23244153
3Dylan1121051
4Amira44534204
5Rhys32334153
6Leah22132102
7Tomos34346204
8Day total151616182085
CellWhat it works outFormula
G2Ffion’s total=SUM(B2:F2)
H2Ffion’s mean=AVERAGE(B2:F2)
B8Monday total=SUM(B2:B7)
G8Grand total=SUM(G2:G7) (or =SUM(B8:F8))

Data entered in the right cells, formulae start with = and use ranges correctly (along a row for a pupil, down a column for a day). The check works: 15 + 16 + 16 + 18 + 20 = 85 and 15 + 5 + 20 + 15 + 10 + 20 = 85. Evidences criteria 1 and 2.

Step 3: debug the formulae

CellFormula typedOK?What goes wrong, and the correct formula
G4=SUM(B4:E4)NoStops at Thursday, so it shows 16, not 20. =SUM(B4:F4)
H3=AVERAGE(B3:F3)YesShows 1, which is correct.
G6=MAX(B6:F6)NoShows Leah’s biggest day, 3, not her total, 10. =SUM(B6:F6)
G5=B5+C5+D5+E5+F5YesShows 15, which is correct, but =SUM(B5:F5) is shorter.
F8SUM(F2:F7)NoNo = at the start, so the cell just shows the words. =SUM(F2:F7) shows 20.

Spots wrong ranges, the wrong function and a missing =, and says what each wrong formula would show. Evidences criterion 3.

Step 4: read the results

(a) Friday, with 20 cups. It was the hottest day (22 °C) and there was a sports afternoon on the field, so pupils were warm and active.

(b) It shows 20. Two pupils, Amira and Tomos, both have 20, and MAX only gives the number, not the name.

(c) =AVERAGE(G2:G7). 85 ÷ 6 = 14.166… = 14.2 cups to 1 decimal place.

(d) 4 pupils: Ffion (15), Amira (20), Rhys (15) and Tomos (20).

(e) 20 × 200 ml = 4,000 ml = 4 litres.

Reads the table accurately, rounds the mean correctly and links Friday’s result to the notes. Evidences criterion 4.

Step 5: true, false or can’t tell?

StatementDecisionReason
(a) Every pupil drank more on Friday than on Monday.FalseDylan went from 1 to 0, and Amira (4) and Leah (2) stayed the same.
(b) Dylan does not drink enough each day.Can’t tellThe log only counts water at school. He might drink water or milk at home. On Friday he left at 10:00, so his 0 is not a full day.
(c) Class 6 drink more water on hot days.Can’t tellFriday was hot and had the most cups, but it was only one week with one hot day, and Friday also had sports. We would need more weeks.
(d) Amira and Tomos drank the same amount at school this week.TrueBoth totals are 20 cups.

(e) Record all drinks for the whole day, not only water at school, so it can be compared with the 6 to 8 cups advice. Do it for more weeks, in hot and cold weather, and with more pupils. Mark when someone is absent instead of writing 0.

Uses exact numbers to prove (a) false, and explains why (b) and (c) could be misleading: the data only covers school time, one week, and a 0 that means absent. Evidences criterion 5.

If you finish early: answers

=MAX(B2:F7) looks at every daily number and shows 6 (Tomos on Friday).

For example: a glass of milk at breakfast, water when you get home, a drink with tea and one in the evening, choosing water or milk instead of squash or fizzy drinks.

Why this response is secure