S5 ICT: Spreadsheet Data Models And Formulas (Paper 3 (challenge))

S5 ICT: Spreadsheet Data Models and Formulas (Paper 3 (challenge))

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 3 (challenge))

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)

FoundationB2 is 120 and C2 is 35 with a 15% discount in B1. What does =ROUND(B2*C2*(1-$B$1),0) return?
FoundationB1 is 5 and B2 holds =B$1*$A2, with A2 = 6 and A3 = 9. Filled down to B3, what are the two results?
FoundationC2 is 0. What does =IFERROR(150/C2,”n/a”) show, and what happens without it?
IntermediateSheet1, Sheet2 and Sheet3 hold 40, 25 and 35 in B2. What does =SUM(Sheet1:Sheet3!B2) return?
IntermediateWith region and month columns, what does =SUMIFS(D2:D100,B2:B100,”HK”,C2:C100,”Jan”) total?
IntermediatePrices are 12, 8 and 15 with quantities 30, 45 and 20. What does =SUMPRODUCT(A2:A4,B2:B4) return?
ChallengeFixed cost is 24,000, price 120 and variable cost 72 per unit. What is the break-even quantity?
ChallengeIn a list A2:A10, =MATCH(“Chan”,A2:A10,0) returns 3. What does that 3 mean?
ChallengeB2:B4 hold 100, 150 and 80, and B3 holds =SUM($B$2:B3). Filled down to B4, what does B4 show?
Error redoA student sorts an order table and correct formulas now return wrong values. Why, and how to avoid it?

03　Answers and one-line notes

#	Answer	One-line note
1	3570.	120 × 35 = 4 200, times 0.85 gives 3 570, which already sits on a whole number.
2	B2 = 30 and B3 = 45.	B$1 locks the row and $A2 locks the column, so filling down only moves the row in $A2: 5 × 6 then 5 × 9.
3	It shows n/a; without IFERROR the cell shows #DIV/0!.	Division by zero is never valid: IFERROR only swaps the error for your text, the division still fails.
4	100.	A 3-D reference adds the same cell across the sheets: 40 + 25 + 35 = 100.
5	Only the D amounts where the region is HK and the month is Jan, which is 2 100 here.	SUMIFS takes the sum range first, then condition range and criterion pairs; every condition must hold.
6	1020.	It multiplies pair by pair then adds: 360 + 360 + 300 = 1 020; the two ranges must be the same size.
7	500 units.	Each unit contributes 120 − 72 = 48, so 24,000 ÷ 48 = 500 units to cover the fixed cost.
8	It is the third item inside the range, not row 3 of the sheet.	MATCH returns a position relative to the range, so pair it with INDEX to pull the whole row.
9	330.	The absolute start stays fixed while the end grows, giving a running total: 100 + 150 + 80 = 330.
10	The formulas point at fixed cells by position, so sorting moves the data but not the reference.	Sorting rearranges rows without rewriting references; use absolute or structured references, or INDEX/MATCH.

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.