/en-tutor-prep-s2-ict-spreadsheet-formulas-practice-2/
/en-tutor-prep-s2-ict-spreadsheet-formulas-practice-2/
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 2: absolute references and percentages

S2 ICT spreadsheet formulas practice 2: absolute references and percentages

  • 2026-11-20

S2 ICT spreadsheet formulas practice 2: absolute references and percentages

Ten items on absolute references such as $B$1, ranges that drift when filled down, shares of a total and COUNTIF criteria, using a 250-dollar by four plus five per cent tax calculation to expose order and format errors.

S2 ICT spreadsheet practice 2: ten advanced items Unit price 250 times quantity 4 gives 1000, plus five per cent tax makes 1050; the four line totals sum to 5000 and the share is 0.2, that is 20 per cent. Ten items on absolute references and fill behaviour, each with a note, focused on ranges that drift.

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 Intermediate

02 Questions (10 items)

  1. FoundationB3 is 250 as unit price and C3 is 4 as quantity. What does =B3*C3 return?
  2. FoundationB1 holds 0.05 as a tax rate. What does =B3*C3*(1+$B$1) return?
  3. FoundationWhy write $B$1 rather than B1 for the tax rate, and how do they differ when filled down?
  4. FoundationD2:D5 hold 1000, 750, 2000 and 1250. What is =SUM(D2:D5)?
  5. IntermediateWhat does =D2/SUM($D$2:$D$5) return, and as a percentage?
  6. IntermediateE2:E9 holds the eight scores above. What is =COUNTIF(E2:E9,”>=60″)?
  7. ChallengeA range holds 80, a blank, 90, the text “absent” and 70. What does AVERAGE return and why?
  8. ChallengeWhat does =ROUND(1234.5678,2) return?
  9. Error redoA student types 5 instead of 5 per cent into the tax cell. What does =B3*C3*(1+B1) become and why is it wrong?
  10. Error redoA student claims that identical answers after filling prove the formula is right. What does that overlook?

03 Answers and one-line notes

# Answer One-line note
1 1000 Unit price times quantity gives the line total.
2 1050 The subtotal times 1.05, that is plus five per cent tax.
3 $B$1 pins one cell while B1 becomes B2, B3 and reads the wrong cell The dollar signs lock both column and row.
4 5000 The four line totals add up to the revenue.
5 0.2, that is 20 per cent A share needs an absolute range as denominator so filling does not drift.
6 7, since only 54 falls below 60 COUNTIF counts the cells meeting the condition.
7 80, because AVERAGE divides only the three numbers by 3 Blanks and text are ignored, so the divisor is 3 rather than 5.
8 1234.57 It rounds to two decimal places.
9 6000, because five per cent must be 0.05 while 5 adds five hundred per cent A percentage inside a formula is a decimal, not a whole number.
10 It overlooks relative references shifting, so ranges and constant cells move too Before filling, ask whether a cell should stay fixed.

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 →