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)
- FoundationB3 is 250 as unit price and C3 is 4 as quantity. What does =B3*C3 return?
- FoundationB1 holds 0.05 as a tax rate. What does =B3*C3*(1+$B$1) return?
- FoundationWhy write $B$1 rather than B1 for the tax rate, and how do they differ when filled down?
- FoundationD2:D5 hold 1000, 750, 2000 and 1250. What is =SUM(D2:D5)?
- IntermediateWhat does =D2/SUM($D$2:$D$5) return, and as a percentage?
- IntermediateE2:E9 holds the eight scores above. What is =COUNTIF(E2:E9,”>=60″)?
- ChallengeA range holds 80, a blank, 90, the text “absent” and 70. What does AVERAGE return and why?
- ChallengeWhat does =ROUND(1234.5678,2) return?
- 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?
- 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.



