S5 ICT | Spreadsheet Modelling and Formulas: Tutor Prep Guide (45-minute flow, script, common errors)

2026-11-15

Tutor lesson-prep guides · Secondary ICT

Students can type =SUM, but the moment they drag the formula down a column it goes wrong, because the reference moved with it. This lesson turns the dollar sign into a rule they can say out loud.

Objectives — By the end of the lesson students can: (1) explain the difference between relative and absolute references and place a dollar sign where a cell must stay fixed; (2) use SUM, AVERAGE, COUNTIF and IF to get a total of 1872, a mean of 62.4, a pass count of 18 and a grade of A, B or C; (3) set data validation that accepts whole numbers from 0 to 100; (4) choose a bar, line or pie chart to match what the question asks.

01 Prerequisites (3-minute check)

If any of these fails, fix it before the lesson starts.

Skill	Quick check	Pass mark
Arithmetic	Thirty students total 1872, so what is the mean	62.4
Percentages	What percentage is 18 out of 30	60 per cent
Logic	Is 68 above 80 and above 60	Above 60, not above 80
Cell addresses	Which column and row is B2	Column B, row 2

02 Lesson flow (45 minutes)

Time	Stage	Tutor does	Student does
0-6 min	Entry check	Ask total over count, the percentage and what B2 means; keep answers on the right of the board	Spoken answers, working on paper
6-16 min	Act one: relative references follow the drag	Drag B2 times C2 down a row and watch the address become B3 times C3	Students say what the address becomes
16-26 min	Act two: the dollar sign pins a cell	Show B2 times $B$1 for a tax rate dragged down thirty rows	Students say what each dollar sign pins
26-36 min	Act three: four formulas, four questions	SUM for totals, AVERAGE for means, COUNTIF for counts, IF for grades	Students write one COUNTIF and one IF
36-42 min	Validation and chart choice	Set whole numbers from 0 to 100, then match chart to question	Students name the question each chart answers
42-45 min	Exit check	Three spoken questions; note which act each error comes from	All three correct

03 Teaching script (read it out)

Scene 1: Act one: a dragged formula changes on purpose

Type =A2*2 in B2 and drag it to B3; the formula becomes =A3*2 by itself. That is a relative reference: the address shifts with the drag. Dragging down thirty rows gives thirty different formulas that look identical. For most of a model this is exactly what you want, since each row works on its own row. The trouble starts when a cell must not move, such as a tax rate sitting in B1: dragged down, it becomes B2, then B3, and every row after the first reads an empty cell.

When the formula reaches the thirtieth row, what has happened to the address inside it?

Scene 2: Act two: the dollar sign is a pin on a column, a row, or both

To stop a cell from following the drag, put a dollar sign in front of it. $B$1 pins both column and row, so it is B1 wherever it lands. B$1 pins only the row, so the column may change; $B1 pins only the column, so the row may change. The rule is that a dollar sign sits in front of whatever you want to pin. In practice press F4 while editing and the spreadsheet cycles through all four forms. The commonest whole-class error is a tax rate written as B1 and dragged down thirty rows, which leaves the later rows multiplying by empty cells and shrinking the total.

What is the difference between $B1 and B$1, and when would you use each?

Scene 3: Act three: four formulas answer four different questions

SUM answers how much altogether, =SUM(B2:B31) giving 1872. AVERAGE answers what the typical value is, =AVERAGE(B2:B31) giving 62.4. COUNTIF answers how many meet a condition, =COUNTIF(B2:B31,”>=60″) giving 18, with the condition in quotation marks. IF answers what category a value falls into, =IF(B2>=80,”A”,IF(B2>=60,”B”,”C”)), testing the higher threshold first; reverse the order and every student becomes an A. The four differ less in syntax than in the question they answer, so read the question for a total, a mean, a count or a category before choosing.

If the question asks how many students passed, which formula would you use, and why?

04 Question types and worked steps

Type	Example	Steps	Answer
Total and mean	Thirty students total 1872; find the mean	AVERAGE over the range rather than dividing by hand	62.4
Conditional count	How many scored 60 or above	COUNTIF with the condition in quotes	18 students, that is 60 per cent
Conditional grade	What grade is 68 with boundaries at 80 and 60	Nested IF testing the higher boundary first	Grade B
Absolute reference	A 0.15 tax rate sits in B1; tax on 500	Write B2*$B$1 and drag down	75

Sanity check (teach this): Three checks: (1) a mean must lie between 0 and 100, and anything outside means the range is wrong; (2) a COUNTIF condition needs quotation marks or the spreadsheet reads it as a cell name; (3) after dragging, open one row and read the formula bar to confirm the dollar signs are still there, a thirty-second habit that saves a rebuilt sheet.

05 Independent practice (3 questions)

Question	Answer	What to watch
A class of 25 totals 1500; what is the mean and which formula gives it	60, using AVERAGE	Check that nobody divided by the wrong count
What does =COUNTIF(C2:C31,”<50″) count	Students scoring below 50	Check the direction of the condition
What is wrong with =IF(B2>=60,”B”,IF(B2>=80,”A”,”C”))	The order is reversed so A can never appear	Test the highest boundary first

06 Common mistakes and what to say

Mistake	Student writes	Say this
Forgetting the dollar signs	Dragging =B2*B1 down thirty rows and losing the total from row three	$B$1 is the pinned form. Press F4 until two dollar signs appear, then drag.
Reversed IF order	Writing =IF(B2>=60,”B”,IF(B2>=80,”A”,”C”))	Test the 80 boundary first, then 60. Reversed, an A can never be produced.
COUNTIF without quotes	Writing =COUNTIF(B2:B31,>=60) and getting an error	A condition is text, so write “>=60”. Without quotes the spreadsheet looks for a cell named that.
Wrong chart for the question	A pie chart comparing five class averages	Pie answers share, bar compares, line shows trend. Ask what the question wants answered first.

07 Homework (paper, section and question numbers)

S5 ICT Practice Paper 1, spreadsheet section — Section B, questions 5 to 8

08 Exit check (last 5 minutes)

Question	Answer	Decision
=B2*$B$1 is dragged thirty rows; what happens to $B$1	It stays $B$1	Wrong: reteach act two, the dollar pin
Thirty students total 1872; what is the mean	62.4	Wrong: reteach act three, AVERAGE
Which formula counts how many students passed	COUNTIF	Wrong: reteach act three, four formulas for four questions

09 Tutor FAQ

Which year level is this for?

Hong Kong S5 ICT asks students to build a spreadsheet model and analyse it with formulas. The S4 work on cell addresses and simple sums is the prerequisite, so revise column and row naming and the feel of =A1+B1 if it is missing.

Is 45 minutes enough?

Yes, but act two needs ten minutes. The dollar sign is the only genuinely new idea; the other three formulas are usually familiar but have never been used together in one sheet.

Does it have to be Excel?

No. Google Sheets and LibreOffice use the same syntax, with identical dollar signs and COUNTIF conditions; only the chart menu sits in a different place.

Find a tutor — free Tutor Registration →

A TutorZone lesson-prep guide. Written by the TutorZone team; questions and lesson flow are original and are not taken from any school or publisher material.

Practice papers　Paper 1 (basics)　·　Paper 2 (intermediate)　·　Paper 3 (challenge)　·　Paper 4 (error clinic)