S5 ICT: Spreadsheet Data Models And Formulas (Paper 1 (basics))

S5 ICT: Spreadsheet Data Models and Formulas (Paper 1 (basics))

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 1 (basics))

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)

FoundationA1 holds 12 and B1 holds 8. What does =A1+B1 return?
FoundationA2 holds 5. What does =A2*2^3 return?
FoundationA1:A5 hold 12, 8, 5, 3 and 2. What does =SUM(A1:A5) return?
IntermediateB1:B4 hold 70, 80, 90 and 60. What does =AVERAGE(B1:B4) return?
IntermediateA1 is 100, B2 is 2 and B3 is 3. B2 holds =$A$1*B2 and is filled down to B3. What are the two results?
ChallengeB2 is 45 and B6 is 180. What does =B2/$B$6 show in percent format?
IntermediateC2 is 48. What does =IF(C2>=50,”Pass”,”Fail”) show?
ChallengeA1:A3 hold 71, 68 and 76. What does =ROUND(AVERAGE(A1:A3),1) return?
ChallengeC2:C6 hold 55, 60, 72, 48 and 90. What does =COUNTIF(C2:C6,”>=60″) return?
Error redoA student types 12 and 8 as text and then uses =A1+B1, which returns #VALUE!. Why?

03　Answers and one-line notes

#	Answer	One-line note
1	20.	The formula takes both cell values and adds them: 12 + 8 = 20.
2	40.	Exponentiation is evaluated first: 2^3 = 8, then 5 × 8 = 40.
3	30.	SUM adds every number in the range: 12+8+5+3+2 = 30.
4	75.	AVERAGE sums to 300 and divides by the four values, giving 75.
5	B2 = 200 and B3 = 300.	$A$1 is absolute, so only the relative part shifts: 100 × 2, then 100 × 3.
6	25%.	45 ÷ 180 = 0.25, shown as 25%; the absolute denominator lets the formula fill down safely.
7	Fail.	48 is not at least 50, so the test is false and the second argument is returned.
8	71.7.	The mean is 215 ÷ 3 = 71.666…, rounded to one decimal place gives 71.7.
9	3.	COUNTIF counts only the cells meeting the test: 60, 72 and 90, so three; the criterion goes in quotes.
10	A cell is stored as text, and text cannot take part in addition.	Numbers entered as text cannot be added; convert the cell back to a number format, or wrap it in VALUE.

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.