中文（繁體）
中文（简体）
English
S4 ICT: Spreadsheet Data Analysis (Paper 4 (error clinic))
Tutor Prep Guide · spreadsheet data analysis: SUM and AVERAGE skipping blanks, COUNT versus COUNTA, COUNTIF and COUNTIFS, relative and absolute references, median and standard deviation, weighted average, pivot tables, chart choice and error values
S4 ICT: Spreadsheet Data Analysis (Paper 4 (error clinic))
Paper 4 (error clinic): ten spreadsheet data analysis questions with answers and one-line notes.
Direct answers Level: S4 ICT; format: statistical functions, conditional counts, references, chart choice, error values and pivot tables.
01 Paper details (check answers afterwards)
| Item | Detail |
|---|---|
| Questions | 10 |
| Suggested time | 20 |
| Difficulty mix | Foundation 3 · Intermediate 3 · Challenge 3 · Error redo 1 |
| Chapter | Chapter: S4 ICT: Spreadsheet Data Analysis |
| Level | S4 |
02 Questions (10 items)
- FoundationA column holds 10, 20, a blank and 30. What does =SUM over the range return?
- FoundationFor the same range, what does =AVERAGE return?
- FoundationIf A1 holds the text absent and A2 holds 40, what does =A1+A2 show?
- IntermediateA formula =A1*2 is copied one row down. What does it become?
- IntermediateA formula =B1*A$1 is copied down to row 4 and one column right. What are its references?
- IntermediateA formula reads =SUM(B2:B10). If a whole row is inserted at row 6, what does the range become?
- ChallengeTo show each subject share of the total when there are four subjects, which chart? And with more than ten subjects?
- ChallengeIn a class of 20, 15 passed at first and 18 passed after remedial teaching. By how many percentage points did the pass rate rise?
- ChallengeThree of five cells hold 60, 80 and 70 with two left blank. What is the average? And if the fourth cell is set to 0 while the fifth stays blank?
- Error redoA student says a chart linked to data will always pick up rows added below it. What is wrong?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | 60 | The three values add to 60 because SUM skips blanks without complaint, so missing entries do not distort the total. |
| 2 | 20.0 | The total 60 over three numeric cells gives 20.0, not the 15.0 you would get by dividing by four. |
| 3 | #VALUE! | The plus sign needs numbers on both sides so text triggers #VALUE!, whereas =SUM(A1:A2) would simply return 40. |
| 4 | =A2*2 | A relative reference shifts with the destination, so one row down turns A1 into A2 instead of copying the formula unchanged. |
| 5 | =C4*B$1 | B1 is fully relative so it moves to C4, while the dollar sign fixes the row of A$1, which shifts one column to B$1. |
| 6 | =SUM(B2:B11) | An inserted row inside the range widens it by one cell, but data appended below the range is never picked up automatically. |
| 7 | A pie chart for four subjects, and a column or stacked bar chart beyond ten | A pie chart suits a handful of categories, but too many slices defeat comparison, so a column or stacked bar chart serves better once categories multiply. |
| 8 | A rise of 15.0 percentage points | The counts give 75 percent and then 90 percent, so the gain is 15.0 percentage points. |
| 9 | 70.0 while blank, and 52.5 once zero is entered | Blanks keep the divisor at three for 70.0, but a zero is a real value, so the divisor becomes four and the mean falls to 52.5. |
| 10 | Wrong: a chart only draws its given range, so new rows outside it never appear unless the range grows or the data becomes a table | A chart draws exactly the range it was given, so rows appended outside it are invisible until the range is extended or the source is turned into a table. |
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.
Always ask what the denominator is: AVERAGE skips blanks while COUNT only counts numbers, so averages and headcounts can mislead; fix relative versus absolute references and choose charts by whether the point is a trend or a share.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.
© TutorZone · www.tutorzone.com.hk · For personal study and teaching use only.