/en-tutor-prep-s2-ict-spreadsheet-formulas-practice-4/
/en-tutor-prep-s2-ict-spreadsheet-formulas-practice-4/
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 4: ten retry items

S2 ICT spreadsheet formulas practice 4: ten retry items

  • 2026-11-20

S2 ICT spreadsheet formulas practice 4: ten retry items

Ten items unpacking the most frequent errors: how AVERAGE and COUNT treat blanks and text, quotes turning numbers into text, percentage format against the real value, #REF! and unlocked denominators, so each slip can be checked off.

S2 ICT spreadsheet practice 4: ten retry items A blank makes AVERAGE divide by 3 rather than 5, COUNT gives 3 where COUNTA gives 4, typing 5 for 0.05 inflates the result to 500 per cent, and ROUND changes the value while formatting does not. Ten items drawn from real marking, each with a note.

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 Error review

02 Questions (10 items)

  1. FoundationA range holds 80, 90 and 70 plus two blank cells. What does AVERAGE return?
  2. FoundationThe same range holds 80, 90, the text “absent”, a blank and 70. What are COUNT and COUNTA?
  3. FoundationWhat does SUM do with a text cell, and why is the total still correct?
  4. FoundationWhen =B2*C2 is filled across to column D, which cell must be locked and what happens if it is not?
  5. IntermediateWhich is correct, =IF(A2>”60″,”pass”,”fail”) or =IF(A2>60,”pass”,”fail”), and why?
  6. IntermediateFormatting 0.05 as a percentage shows 5 per cent. What happens if the value itself becomes 5?
  7. ChallengeAfter a column is deleted a formula shows #REF!. What caused it and how is it fixed?
  8. ChallengeWhy is a line chart wrong for showing the share each of four quarters contributes to total revenue?
  9. Error redoWhat is the practical difference between =ROUND(74.875,1) and formatting 74.875 to one decimal?
  10. Error redoA student uses =D2/SUM(D2:D5) for shares and the filled column totals over one hundred per cent. What is wrong?

03 Answers and one-line notes

# Answer One-line note
1 80, because blanks never enter the divisor The divisor is 3 rather than 5, the most frequent slip of all.
2 COUNT is 3 and COUNTA is 4 COUNTA counts text too while both ignore blanks.
3 Text is ignored and only numeric cells are added SUM silently skips non-numbers, which is exactly why mistakes hide.
4 The constant cell needs $B$1; unlocked it points at the next row and reads a blank or wrong number Before filling, check which references should stay fixed.
5 The second, because a score is a number and quotes turn it into a text comparison Quotes belong only around text results.
6 It shows 500 per cent and every dependent formula grows a hundredfold Percentage formatting only changes display; the stored value stays decimal.
7 The referenced column was deleted, so the range must be rewritten #REF! means the reference no longer exists.
8 A line chart shows trend; part of whole needs a pie or a hundred per cent stacked bar With only four parts a pie chart is clearest.
9 ROUND stores 74.9 while formatting shows 74.9 but keeps 74.875 Round when the number feeds further maths, format when it is only for display.
10 The denominator range is unlocked, so it drifts when filled; it must be $D$2:$D$5 A denominator should always be absolute.

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 →