Supply teacher deck · Food and Nutrition · Year 6

Planning a Water Tracker Spreadsheet

Slide 1 of 7
05:00

Slide 1 of 7 · Settling starter · Food and Nutrition · Year 6

5 minutes

Planning 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?

DCF: Data and computational thinking → Data and information literacy No computers needed · work on your sheet

Slide 2 of 7 · Today’s brief and key words · Food and Nutrition · Year 6

4 minutes

Your 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.

LNF: Numeracy → Statistics and probability → Interpreting data Keep the help card next to you all lesson

Slide 3 of 7 · Steps 1 and 2: enter the data and write the formulae · Food and Nutrition · Year 6

18 minutes

Steps 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
Steps 1 and 2 on your worksheet · 18 minutes Check: column G and row 8 must both add up to the same G8

Slide 4 of 7 · Step 3: debug the formulae · Food and Nutrition · Year 6

8 minutes

Step 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 =?

Step 3 on your worksheet · 8 minutes A formula with no = just shows as words

Slide 5 of 7 · Steps 4 and 5: read the results and test the conclusions · Food and Nutrition · Year 6

16 minutes

Steps 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.

Steps 4 and 5 on your worksheet · 16 minutes Remember: the log only counts water at school

Slide 6 of 7 · Self and partner check · Food and Nutrition · Year 6

3 minutes

Check 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.
3 minutes · partner check Finished? Try the two questions under “If you finish early”

Slide 7 of 7 · Plenary review · Food and Nutrition · Year 6

3 minutes

Quick-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.
Check your name and class are on your worksheet Leave stimulus sheets neat on the desk