/en-tutor-prep-s3-ict-spreadsheet-formulas-functions-practice-3/
/en-tutor-prep-s3-ict-spreadsheet-formulas-functions-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

Paper 3: Challenge: nested logic, lookups and errors

Paper 3: Challenge: nested logic, lookups and errors

  • 2026-11-21

Paper 3: Challenge: nested logic, lookups and errors

Chapter practice with ten items, answers and a one-line note for each, about 25 minutes.

Paper 3 Ten items on this chapter with answers for immediate marking.

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: S3 ICT: spreadsheet formulas and functions
Level Challenge

02 Questions (10 items)

  1. FoundationWhat does `=SUM(D2:D6)*0.93` give?
  2. FoundationWith `=IF(D2>=300,”A”,IF(D2>=100,”B”,”C”))`, what do D2 = 66, D3 = 336 and D5 = 111 give?
  3. FoundationThe grade table A2:B6 holds 0 to F, 60 to D, 70 to C, 80 to B and 90 to A. What does `=VLOOKUP(78,$A$2:$B$6,2,FALSE)` return?
  4. FoundationThe amounts are 66, 336, 387, 80 and 111. What totals the three largest, and what is the result?
  5. IntermediateWhat is the mean of the same amounts after leaving out the lowest one?
  6. IntermediateHow does `=SUMPRODUCT(B2:B6,C2:C6)` relate to `=SUM(D2:D6)`?
  7. ChallengeThe five original rows total 980. Adding a row of 10 items at 9.9 each gives what new total and new mean?
  8. ChallengeD2 is 66 and D7 is 980. What does `=ROUND(D2/$D$7*100,2)` give?
  9. Error redoWhat usually causes a cell to show `#DIV/0!`?
  10. Error redoB2 holds 12 and C2 holds 5.5. What does `=B2&C2` return?

03 Answers and one-line notes

# Answer One-line note
1 911.4. The total of 980 is multiplied by 0.93, which is a 7 per cent reduction.
2 C, A and B. A nested IF tests 300 first, then 100, and only falls through to C when both fail.
3 C. The nearest lower band is the one starting at 70.
4 `=LARGE(D2:D6,1)+LARGE(D2:D6,2)+LARGE(D2:D6,3)`, which gives 834. LARGE returns the nth largest value, so 387 plus 336 plus 111 gives 834.
5 228.5. Subtracting the lowest 66 from the total 980 leaves 914, which is then divided by four.
6 Both give 980 because SUMPRODUCT multiplies the two columns row by row and adds the results without a helper column. SUMPRODUCT removes the need for a helper column when totalling money.
7 A new total of 1079 and a new mean of 179.83. The new row adds 99, and dividing by six gives 179.833…, rounded to two decimals.
8 6.73. The locked $D$7 keeps the same denominator, so every filled row divides by the same total.
9 The divisor is zero, or the divisor cell is blank. An IF test on the divisor avoids a whole column of errors.
10 The text 125.5, which can no longer be multiplied. The ampersand joins values as text, so numbers lose their arithmetic meaning.

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.

Want the whole chapter? See all four difficulty papers on the chapter page.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 →