中文（繁體）
中文（简体）
English
S4 ICT: Spreadsheet Data Analysis (Paper 2 (intermediate))
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 2 (intermediate))
Paper 2 (intermediate): 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)
- FoundationSix students score 45, 52, 68, 71, 83 and 88. What is the mean, to one decimal place?
- FoundationWhat is the median of the same six marks?
- FoundationA subject mean rises from 250 to 300. What is the percentage increase?
- IntermediateCoursework scores 72 and counts for 30 percent, the exam scores 65 and counts for 70 percent. What is the weighted mean?
- IntermediateAmong 20 students, 7 are girls scoring 80 or above. What does =COUNTIFS(gender,”F”,score,”>=80″) return and what share of the class is that?
- IntermediateEight marks are 91, 88, 85, 79, 73, 68, 60 and 55. What does =LARGE(B2:B9,2) return?
- ChallengeWithin those eight marks, what rank does 88 take from =RANK(B3,B2:B9)?
- ChallengeA school has 120 students and class A has 30, or 25.0 percent. If class A gains 6 students and nothing else changes, what is the new share?
- ChallengeA cell shows #DIV/0! because C2 is blank. What is the most direct fix?
- Error redoA student says filtering for girls and copying the cells to a new sheet moves only the girls. What is wrong?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | 67.8 marks | The six marks sum to 407, and dividing by six gives about 67.83, or 67.8 to one decimal place. |
| 2 | 69.5 marks | The two middle values after sorting are 68 and 71, and their mean is 69.5, a figure extremes cannot drag. |
| 3 | 20.0 percent | The change of 50 over the original 250, times 100, is 20.0 percent, so the original value is the base. |
| 4 | 67.1 marks | The 72 gives 21.6 and the 65 gives 45.5, and the two parts add to 67.1. |
| 5 | 7 students, 35.0 percent of the class | COUNTIFS counts only rows meeting both tests, giving 7, and dividing by 20 times 100 gives 35.0 percent. |
| 6 | 83 marks | LARGE returns the k-th largest value, so k of two gives the second highest, 83, wherever it sits in the sheet. |
| 7 | Rank 2 | Only the 91 is higher than 88, so it ranks 2, since RANK compares values rather than row order. |
| 8 | 28.6 percent | The new part is 36 over a new whole of 126, which is 28.6 percent; when both change, recompute the share. |
| 9 | Use =IFERROR(B2/C2,0), or make sure C2 holds a number | Dividing by zero or a blank gives #DIV/0!, and IFERROR replaces the error with zero or a blank, though that zero is a substitute rather than a measurement. |
| 10 | Wrong: a plain copy also takes the hidden rows, so copy only visible cells or filter after moving the data | Filtering only hides the rows that do not match while the data stays in place, so a plain copy brings them along and the pasted block is not what was on screen. |
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.