S5 ICT: Spreadsheet Data Models And Formulas (Paper 2 (intermediate))

S5 ICT: Spreadsheet Data Models and Formulas (Paper 2 (intermediate))

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 2 (intermediate))

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:B5 hold A, A, B, A and C2:C5 hold 10, 20, 30 and 40. What does =SUMIF(B2:B5,”A”,C2:C5) return?
FoundationUsing the same data, what does =AVERAGEIF(B2:B5,”A”,C2:C5) return to one decimal place?
FoundationA1:A5 hold 45, 82, 37, 90 and 68. What does =MAX(A1:A5)-MIN(A1:A5) return?
IntermediateB2 holds =A$1+A2, with A1 = 3, B1 = 4 and B2 = 10. B2 is filled right into C2. What does C2 show?
IntermediateB2 is 62 and C2 is “Y”. What does =IF(AND(B2>=50,C2=”Y”),”Accept”,”No”) show?
IntermediateA1:A5 contain three numbers, one text cell and one blank. What do =COUNT and =COUNTA return?
ChallengeA1 is 75. What does =IF(A1>=80,”A”,IF(A1>=60,”B”,”C”)) show?
ChallengeWhen VLOOKUP looks up a grade band, should the last argument be FALSE or TRUE, and why?
ChallengeThe markup in B1 is 12% and the cost in B2 is 250. What does =B2*(1+$B$1) return?
Error redoA student writes =VLOOKUP(G2,A2:D10,3) with no fourth argument, and the grade is sometimes wrong. Why?

03　Answers and one-line notes

#	Answer	One-line note
1	70.	SUMIF adds only rows where the test range equals A: 10+20+40 = 70, and the 30 on the B row is ignored.
2	23.3.	The three matching values average 70 ÷ 3 = 23.3, not the 25 you get from all four cells.
3	53.	The maximum is 90 and the minimum 37, so the range is 53.
4	14.	The row is locked and the column is relative, so it becomes =B$1+B2, giving 4 + 10 = 14.
5	Accept.	AND needs both tests true: 62 is at least 50 and C2 is “Y”, so the first argument is returned.
6	COUNT = 3 and COUNTA = 4.	COUNT counts numeric cells only, while COUNTA counts every non-empty cell, including text.
7	B.	75 fails the 80 test, so the second IF runs, and 75 is at least 60, giving B.
8	Use FALSE for an exact match; TRUE is approximate and returns the largest value not above the lookup value.	Grade tables are not a continuous number line, so TRUE silently returns the wrong band. Always use FALSE.
9	280.	1 + 12% = 1.12 and 250 × 1.12 = 280; the absolute reference keeps the rate fixed when filled down.
10	Without FALSE the lookup is approximate, which assumes the first column is sorted ascending.	Approximate matching pairs rows wrongly on unsorted data; adding FALSE makes it an exact 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.