S5 ICT: Spreadsheet Data Models And Formulas (Paper 4 (error Clinic))

S5 ICT: Spreadsheet Data Models and Formulas (Paper 4 (error clinic))

2026-11-15

Tutor Prep Guide · Cell references (relative, absolute and mixed), operator precedence, the SUM, AVERAGE and COUNT family, IF and nested IF, exact-match VLOOKUP, error values and debugging

S5 ICT: Spreadsheet Data Models and Formulas (Paper 4 (error clinic))

Ten spreadsheet data-model questions on references, summary functions, conditional tests and error values, with answers and one-line notes.

Level: S5 ICT; format: cell references, operator precedence, SUM, AVERAGE and COUNTIF, IF and nested IF, VLOOKUP, SUMIFS, SUMPRODUCT, error values and model estimates.　S5 ICT spreadsheet data-model practice: ten questions on relative and absolute references, operator precedence, summary and conditional functions, lookup functions, error values and break-even style estimates, each with an answer and a one-line note.

01　Paper details (check answers afterwards)

Item	Detail
Questions	10
Suggested time	20
Difficulty mix	Foundation 3 · Intermediate 3 · Challenge 3 · Error redo 1
Chapter	Chapter: S5 ICT: Spreadsheet Data Models and Formulas
Level	S5

02　Questions (10 items)

FoundationAfter deleting a whole column a formula shows #REF!. What does that mean?
FoundationB2 is 0 and =A2/B2 shows #DIV/0!. How should the formula be written safely?
FoundationA student types =SUMM(A1:A5) and the cell shows #NAME?. Why?
IntermediateA1 is 3, and =$A$1+A1 sits in B1 and is filled down to B5. Are all five results the same?
IntermediateA cell holds 2.345 and displays 2.35 in two-decimal format. Does a total use 2.35 or 2.345?
IntermediateThe formula =IF(C2>=50,Pass,Fail) is missing quotes. What error appears?
ChallengeB2:B5 hold apple, banana, avocado and cherry. What does =COUNTIF(B2:B5,”a*”) return?
ChallengeA1:A4 hold 80, 0, a blank and 60. What do =AVERAGE(A1:A4) and =COUNT(A1:A4) return?
ChallengeA1 is 90 and B1 is 85. What does =A1>B1 show, and what type is it?
Error redoA student pastes 0.25 into a percent column and every percentage is out by a factor of 100. What went wrong?

03　Answers and one-line notes

#	Answer	One-line note
1	The reference is broken: the column or row it pointed at no longer exists.	#REF! is a reference error; undo the deletion or repoint the formula at an existing range.
2	Use =IFERROR(A2/B2,0), or test whether B2 is zero first.	Division by zero always errors; IFERROR hides it, so also check whether the data itself is wrong.
3	The function name is misspelled, so SUMM is not recognised.	#NAME? almost always means a misspelled name or missing quotes; use autocomplete to avoid it.
4	No: $A$1 stays 3 while A1 becomes A2, A3 and so on, so each row adds a different value.	An absolute reference stays put while a relative one travels, so mixing them gives different results per row.
5	It uses the stored 2.345; formatting changes the display only, not the value.	To make totals agree with the display, apply ROUND to change the value rather than the format.
6	#NAME?, because unquoted text is treated as a defined name.	Text results inside IF must be quoted; otherwise the sheet looks for a range with that name.
7	3.	The asterisk matches any characters after the a, so apple and avocado count, but banana does not.
8	AVERAGE = 46.7 and COUNT = 3.	AVERAGE skips blanks but still counts the zero, so 140 ÷ 3; COUNT likewise gives three.
9	It shows TRUE, which is a Boolean value usable directly inside IF.	A comparison returns TRUE or FALSE, not text; wrap it in IF when you need words.
10	0.25 already is 25%, so percent formatting shows 25%; if the column expects the number 25 you must multiply by 100.	Percent formatting is display only and the value stays a decimal; agree whether you exchange 0.25 or 25.

04　What to do after marking

After marking, redo only the wrong items and write down which step went wrong (formula, unit, or misread number).
Repeat the same item two days later; move to the next paper only when both attempts are correct.
Parents only need the answer and note columns; there is no need to explain every item.

Always ask first where a formula will be filled to, because the fill direction decides which references need dollar signs. When an error appears, separate a broken reference (#REF!) from division by zero (#DIV/0!) and a bad name (#NAME?) before editing. Use FALSE for exact lookups, and apply ROUND when the value itself must change rather than the display.Tutor Registration →

Questions and answers are written originally by the TutorZone team and are not taken from any school, publisher or examination authority paper. Every value is checked by computer.