S2 ICT spreadsheet formulas practice 3: nested IF, VLOOKUP and chart choice
- 2026-11-20
S2 ICT spreadsheet formulas practice 3: nested IF, VLOOKUP and chart choice
Ten items gathering nested IF, AND, SUMIF, IFERROR and exact-match VLOOKUP, closing with chart selection and mixed text and number types, training students to name the data relationship before choosing a tool.
S2 ICT spreadsheet practice 3: ten challenge items A score of 78 returns B from a nested IF, the AND test fails on 58, SUMIF over department A gives 470 and ROUNDUP lifts 78 to 80. Each item carries a note, including what FALSE and TRUE mean in VLOOKUP.
01 Paper details (check answers afterwards)
| Item | Detail |
|---|---|
| Questions | 10 |
| Suggested time | 25 |
| Difficulty mix | Foundation 4 · Intermediate 2 · Challenge 2 · Error redo 2 |
| Chapter | Chapter: S2 ICT: Spreadsheet formulas and charts |
| Level | Challenge |
02 Questions (10 items)
- FoundationA2 holds 78. What does =IF(A2>=80,”A”,IF(A2>=70,”B”,”C”)) return?
- FoundationFor =IF(AND(B2>=60,C2>=60),”pass”,”fail”) with B2 65 and C2 58, what is returned?
- FoundationAmounts 120, 80, 300 and 50 sit against departments A, B, A and A. What is =SUMIF(dept,”A”,amount)?
- FoundationWhat do =IFERROR(100/0,”error”) and =100/0 each show?
- IntermediateWhat does =ROUNDUP(78/10,0)*10 return and where is that pattern used?
- IntermediateWhich chart shows the trend of monthly sales, and which shows each subject share of the total?
- ChallengeA student fills =COUNTIF(A2:A9,”>=60″) downward and the range becomes A3:A10. What goes wrong?
- ChallengeWhat does =VLOOKUP(102,table,3,FALSE) show when 102 is missing from the first column, and what if the last argument is TRUE?
- Error redoA student types the score as text “78” then compares it with 60. What happens?
- Error redo=SUM(E2:E9) returns 0 although numbers are visible. What are the three likeliest causes?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | “B” | A nested IF tests outward in, so 78 fails 80 then passes 70. |
| 2 | fail, because AND needs both conditions true | One false input makes AND false. |
| 3 | 470 | SUMIF adds only the rows that match the criterion. |
| 4 | “error” and #DIV/0! | Dividing by zero raises an error that IFERROR catches. |
| 5 | 80, used to round up to the nearest ten, for example packing or class size | ROUNDUP always moves away from zero. |
| 6 | A line chart for trend and a pie chart for shares | Match chart to relationship: time series takes a line, part of whole takes a pie. |
| 7 | The range drifts, the first row is dropped and the count comes out short | Pinning the range needs $A$2:$A$9. |
| 8 | #N/A with FALSE, while TRUE returns an approximate match that looks valid but may be the wrong row | Use FALSE for exact matching; approximate matching can be quietly wrong. |
| 9 | Comparing text with a number fails to give the right TRUE or FALSE, so the condition breaks | IF and COUNTIF both need matching data types. |
| 10 | Numbers stored as text, the range pointing at the wrong column, or a circular reference in the formula cell | Check alignment and the warning triangle first, then the range. |
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.
The commonest spreadsheet failure is a correct formula with a reference one cell off. Book a TutorZone tutor and spend twenty-five minutes checking SUM, AVERAGE, IF and absolute references cell by cell, plus COUNT against COUNTA.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.



