中文（繁體）
中文（简体）
English
S4 ICT: Spreadsheet Data Analysis (Paper 1 (basics))
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 1 (basics))
Paper 1 (basics): 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)
- FoundationFive students score 62, 78, 85, 90 and 95. What total does =SUM(B2:B6) return?
- FoundationFor the same five marks, what does =AVERAGE(B2:B6) return?
- FoundationWhat does =MAX(B2:B6)-MIN(B2:B6) return for the same marks?
- IntermediateFour cells hold 60, 80, 70 and 90 with a fifth cell left blank. What does =AVERAGE over the five cells return?
- IntermediateIn a five-cell range four cells hold numbers and one holds the word absent. What do COUNT and COUNTA return?
- IntermediateA formula =B2*$B$10 is copied from row 2 down to row 5. What does it become?
- ChallengeOf 20 students, 15 score 60 or above. What does =COUNTIF(B2:B21,”>=60″) return and what is the pass rate?
- ChallengeA student scores 58. What does =IF(B2>=60,”pass”,”fail”) display?
- ChallengeIf the target pass rate is 80 percent for a class of 20, how many students must pass?
- Error redoA student says =A1+A2 and =SUM(A1:A2) always give the same result. What is wrong?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | 410 marks | The five marks add up to 410, and SUM quietly skips any blank or text cell in the range. |
| 2 | 82.0 marks | Dividing 410 by five gives 82.0, and that five is exactly the number of numeric cells. |
| 3 | 33 marks | The highest 95 minus the lowest 62 is 33, the range, which ignores everything in between. |
| 4 | 75.0 | The four cells sum to 300 and AVERAGE divides by four, giving 75.0, because a blank does not join the denominator. |
| 5 | COUNT returns 4 and COUNTA returns 5 | COUNT counts only the numeric cells, giving 4, while COUNTA counts text as well and returns 5. |
| 6 | =B5*$B$10, with $B$10 unchanged | B2 is relative so three rows down it becomes B5, while the dollar signs freeze $B$10 to the same cell. |
| 7 | 15 students, a pass rate of 75.0 percent | COUNTIF counts only the matching cells, giving 15, and dividing by 20 then multiplying by 100 gives 75.0. |
| 8 | fail | The score 58 is below 60 so the test is false and IF returns the second value, fail. |
| 9 | 16 students | Multiplying 20 by 80 and dividing by 100 gives 16, so 16 passing students are needed. |
| 10 | Wrong: if a cell holds text the plus sign returns #VALUE! while SUM treats it as zero | Arithmetic needs numbers in both cells and errors on text, whereas SUM tolerates text and blanks, so the two formulas can differ on the same data. |
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.