/en-tutor-prep-s2-ict-spreadsheet-formulas-practice-3/
/en-tutor-prep-s2-ict-spreadsheet-formulas-practice-3/
General Inquiry: info@TutorZone.com.hk
Office Hours: Monday to Saturday - 9:00 AM to 6:00 PM
WhatsApp Monday to Sunday - 24 hours
Hotline: +852 6828 1809
Instant Support: +852 9061 3106

S2 ICT spreadsheet formulas practice 3: nested IF, VLOOKUP and chart choice

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)

  1. FoundationA2 holds 78. What does =IF(A2>=80,”A”,IF(A2>=70,”B”,”C”)) return?
  2. FoundationFor =IF(AND(B2>=60,C2>=60),”pass”,”fail”) with B2 65 and C2 58, what is returned?
  3. FoundationAmounts 120, 80, 300 and 50 sit against departments A, B, A and A. What is =SUMIF(dept,”A”,amount)?
  4. FoundationWhat do =IFERROR(100/0,”error”) and =100/0 each show?
  5. IntermediateWhat does =ROUNDUP(78/10,0)*10 return and where is that pattern used?
  6. IntermediateWhich chart shows the trend of monthly sales, and which shows each subject share of the total?
  7. ChallengeA student fills =COUNTIF(A2:A9,”>=60″) downward and the range becomes A3:A10. What goes wrong?
  8. ChallengeWhat does =VLOOKUP(102,table,3,FALSE) show when 102 is missing from the first column, and what if the last argument is TRUE?
  9. Error redoA student types the score as text “78” then compares it with 60. What happens?
  10. 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.

Join as a tutor or coach — academic, music, sports, arts, STEM. Free to register. See how it works →