Cover lesson · Health and Well-being · Year 6 · Advanced

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

Name: Class: Date:

Cover teacher: you need no subject knowledge

What this practises: entering data into a spreadsheet layout, writing SUM, AVERAGE, MAX and MIN formulae, and fixing formulae that give the wrong answer, all on paper. Pupils then use the results to answer questions, find means and decide which conclusions the data supports and which could be misleading, so Steps 4 and 5 are real numeracy evidence of interpreting data.

Print: this sheet, one per pupil, and the stimulus sheet “Class 6 water challenge log”, one between two. No computers are needed. If computers are free, pupils can type the spreadsheet to check their answers after Step 2, but the lesson works on paper.

Run: 5 min read the stimulus sheet aloud, especially the help card · 8 min Step 1 · 12 min Step 2 · 8 min Step 3 · 12 min Step 4 · 12 min Step 5. Total 57 minutes.

Collect: this sheet. Steps 4 and 5 are the numeracy evidence (answers read from the totals and means, the mean of the totals, and true, false or can’t tell decisions with reasons). Steps 1, 2 and 3 are the digital competence evidence. Quick checks: 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 to 1 decimal place.

What you are doing

Class 6 at Ysgol Penrhos recorded how many cups of water they drank at school for one week. You will plan the spreadsheet for their results on paper, write the formulae, fix some broken formulae, and then decide what the data really tells us about drinking water.

Step 1: enter the data

Copy the numbers from the paper log into the spreadsheet. Row 1 and column A are done for you. Leave columns G and H and row 8 empty for now.

ABCDEFGH
1PupilMonTueWedThuFriTotalMean
2Ffion
3Dylan
4Amira
5Rhys
6Leah
7Tomos
8Day total

Step 2: write the formulae

(a) Write the formula for each cell. Use the help card.

CellWhat it should work outFormula
G2Ffion’s total for the week
H2Ffion’s mean cups a day
B8Total cups on Monday
G8Total cups for everyone, all week

(b) Now work out what each formula would show, and fill in every empty cell in columns G and H and row 8 of your spreadsheet. Check: the day totals in row 8 and the pupil totals in column G should both add up to the same number in G8.

Step 3: debug the formulae

Another group typed these formulae. Some are wrong. Tick the right ones. For each wrong one, say what it would show and write the correct formula.

CellFormula typedOK?What goes wrong, and the correct formula
G4=SUM(B4:E4)
H3=AVERAGE(B3:F3)
G6=MAX(B6:F6)
G5=B5+C5+D5+E5+F5
F8SUM(F2:F7)

Step 4: read the results

(a) Which day had the most cups? Use the notes on the stimulus sheet to suggest why.

(b) The group types =MAX(G2:G7). What number does it show? Why does it not tell you who drank the most?

(c) Write a formula for the mean of all 6 totals. Work it out to 1 decimal place.

(d) How many pupils have a total above that mean? Who are they?

(e) Each cup is 200 ml. How many litres did Amira drink at school this week?

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

Decide whether the data shows each statement is true, false, or whether you can’t tell. Give a reason using the data or the notes.

StatementTrue, false or can’t tell?Reason
(a) Every pupil drank more on Friday than on Monday.
(b) Dylan does not drink enough each day.
(c) Class 6 drink more water on hot days.
(d) Amira and Tomos drank the same amount at school this week.

(e) Suggest 2 changes to the water challenge so the Healthy Schools group can trust their conclusions more.

Step by step

  1. 5 min Read the stimulus sheet and the help card with your teacher.
  2. 8 min Step 1: enter the data into the spreadsheet.
  3. 12 min Step 2: write the formulae and fill in the results.
  4. 8 min Step 3: debug the broken formulae.
  5. 12 min Step 4: read the results.
  6. 12 min Step 5: decide what the data really shows.

Success criteria

If you finish early