S3 ICT | Spreadsheet Formulas and Functions Prep Guide (for tutors) | Twelve months of data and the $ lock in 60 minutes

2026-11-06

Tutor lesson-prep guides · Secondary ICT

A student gets 52.17 on a calculator but types 52 into the spreadsheet, because he believes the cell can only hold whole numbers. The arithmetic is fine; the idea that a formula and its display are separate has never been taught.

Objectives — Students can: (1) write formulas such as =B2*C2 and state the order of operations (brackets, then powers, then multiply and divide, then add and subtract); (2) use SUM, AVERAGE, MAX and MIN on twelve monthly sales figures to get a total of 626, a mean of 52.17 and a maximum of 66; (3) explain what $ does and keep a fixed tax cell such as $B$1 while copying a formula down the column.

01 Prerequisites (3-minute check)

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

Skill	Quick check	Pass mark
Cell addresses	Which cell is C5?	Column C, row 5
Order of operations	Which comes first, multiply or add?	Multiply, unless brackets say otherwise
Percentages	How do you write 52.17 as a percentage?	5217%

02 Lesson flow (60 minutes)

Time	Stage	Tutor does	Student does
0-5 min	Entry: two answers	Same figures, calculator says 52.17, the cell shows 52	Suggest why they differ
5-15 min	Formulas and order	Model =B2C2 and =(B2+C2)D2, name the order	Write three formulas and state the order
15-30 min	Four functions	Model SUM, AVERAGE, MAX and MIN on twelve months	Enter four functions and check the results
30-45 min	$ locks a cell	Model $B$1 for a tax rate, copied down twelve rows	Rewrite four formulas with $
45-55 min	Chart choice	Column charts compare categories, line charts show trend	Pick a chart for two data sets and justify it
55-60 min	Exit check	Three items collected and marked	Work independently

03 Teaching script (read it out)

Scene 1: A formula recalculates; typing does not

Typing =B2*C2 stores the formula and shows the result. Change B2 and the result updates – that is what a spreadsheet does that a calculator cannot.
Display and value are separate: the cell may hold 52.17 and show 52 because the format is set to zero decimals. Students think they are wrong when only the format differs, so teach them to read the formula bar rather than the cell face.
Order of operations matches mathematics: brackets, powers, multiply and divide, add and subtract. =(B2+C2)*D2 is not the same as =B2+C2*D2.

Students do: write the same calculation twice, once with brackets and once without, and explain the difference.

Scene 2: Four functions cover most questions

The twelve monthly figures are 42, 38, 51, 47, 60, 55, 49, 58, 63, 52, 45 and 66, totalling 626.
SUM(B2:B13) gives 626; AVERAGE(B2:B13) gives 52.17; MAX gives 66; MIN gives 38. Write the range with a colon from first to last cell, never add cells one by one.
AVERAGE matches “total divided by 12” today, but when a row is added AVERAGE follows the range while a hand-written 12 does not.
Function names ignore case, but brackets and colons must be half-width.

Students do: enter the four functions, change 66 to 96 and note which of the four results move.

Scene 3: $ is a lock, and the chart is the conclusion

$B$1 locks both column and row when the formula is copied. Plain B1 shifts to B2, B3 and so on, pointing at the wrong cell.
Rule: lock both with two dollar signs; lock one axis with $B1 or B$1. Tax rates, exchange rates and discounts – one cell shared by the whole table – must be locked.
A chart is not decoration: columns compare categories, lines show trend over time, pie shows share. The wrong chart misleads even when the numbers are right.

Students say: why the tax cell must be $B$1 and not B1 (copying shifts it to an empty cell, so the result collapses to zero).

04 Question types and worked steps

Type	Example	Steps	Answer
Write a formula	Sales equals quantity times unit price; write D2	Find the two cell addresses first	=B2*C2
Use a function	Total of B2 to B13	SUM with a range	=SUM(B2:B13)
Absolute reference	Add 5% tax so the formula copies down	Lock the tax cell with $	=B2*(1+$B$1)
Chart choice	Show how sales changed over twelve months	Trend means line chart	Line chart

Sanity check (teach this): Sanity check: every formula starts with =; brackets come in pairs; ranges use a colon and arguments use commas; an AVERAGE must sit between MIN and MAX (52.17 lies between 38 and 66, so anything outside means the range is wrong); after copying, check each cell address; the chart type must match comparison or trend.

05 Independent practice (3 questions)

Question	Answer	What to watch
Write formulas	Three basic formulas	Check for the leading = sign
Four functions	Total, mean, maximum and minimum of twelve months	Check 626 and 52.17
Add $	Two formulas made copy-safe	Check the right axis is locked

06 Common mistakes and what to say

Mistake	Student writes	Say this
Missing =	Typing B2*C2 leaves text	“Without the equals sign the spreadsheet treats it as a word, not a calculation.”
Adding cells one by one	=B2+B3+B4 twelve times	“One SUM(B2:B13) does it. Twelve additions break the moment the data changes.”
Locking the wrong axis	=$B2 still shifts when copied down	“You locked the column but not the row. Lock both: $B$1.”
Reading the display	Seeing 52 when the cell holds 52.17	“Read the formula bar, not the cell face. Value and format are two different things.”

07 Homework (paper, section and question numbers)

Secondary ICT chapter index — All chapters for this subject
Prep guide index — The same kind of chapter for other subjects
Redo this chapter — Build a twelve-month table with a 5% tax cell and a line chart

08 Exit check (last 5 minutes)

Question	Answer	Decision
Every formula must start with	= (the equals sign)	Answering a number means act one again
Total of B2 to B13	=SUM(B2:B13)	Answering =B2+B13 means act two again
To lock one cell completely write	$B$1	Answering B1 means act three again

09 Tutor FAQ

Are Excel and Google Sheets formulas the same?

All four functions and the $ notation behave the same. The menus differ, and exams normally test only the shared parts.

Must students memorise function names?

The four basics, yes. Others can be looked up; marks come from the structure of the formula, not from memory.

Do they have to draw charts by hand?

Written papers usually test choice and reasoning – which chart and why – so the justification matters more than the drawing.

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.