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



