中文（繁體）
中文（简体）
English
S4 ICT: Spreadsheet Data Analysis (Paper 3 (challenge))
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 3 (challenge))
Paper 3 (challenge): 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 subject combines coursework 78 at 20 percent, a test 85 at 30 percent and the exam 62 at 50 percent. What is the weighted mean?
- FoundationWhat is the population standard deviation of the four values 2, 4, 4 and 10?
- FoundationAn index rises from 1000 to 1331 over three years. What is the annual compound growth rate?
- IntermediateTo show how the class spreads across score bands from 0 to 100, which chart is most suitable?
- IntermediateIn a pivot table class A totals 320 in Chinese and class B totals 280. What is the combined total, and what is class A average with 8 students?
- IntermediateA formula =VLOOKUP(“D”,A2:C50,3,FALSE) finds no D in the first column. What appears?
- ChallengeA sheet has 1000 rows and 12 percent fall in one category. How many rows does =COUNTIF return, and how many if half of them are sampled?
- ChallengeSales were 240,000 in year one and 300,000 in year two, then fell ten percent in year three. What is the year three figure?
- ChallengeIn a class of 20, 17 score 60 or above and 12 score 70 or above. If the pass mark rises from 60 to 70, how many fewer students pass?
- Error redoA student says that when the mean exceeds the median, half the class must be above the mean. What is wrong?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | 72.1 marks | Each mark times its weight adds to 15.6 plus 25.5 plus 31.0, which is 72.1, and the weights must total one. |
| 2 | 3.0 | The mean is 5 and the squared deviations sum to 36, which over four gives a variance of nine and a standard deviation of 3.0. |
| 3 | 10.0 percent per year | The ratio of 1331 to 1000 is 1.331, whose cube root is 1.1, so the rate is 10.0 percent a year. |
| 4 | A histogram | A histogram bins continuous values and the bar height is the count in each bin, whereas a line chart shows trend and a pie chart shows shares. |
| 5 | 600 in total, and class A averages 40.0 | The two totals add to 600 while 320 over eight gives 40, which shows why a pivot total is not an average. |
| 6 | #N/A | The FALSE argument demands an exact match, so a missing key returns #N/A, while TRUE would return the nearest smaller value and invite mismatches. |
| 7 | 120 rows, a sample of 60 | 0.12 times 1000 is 120 rows and half of that is 60, since a percentage must be used as a decimal rather than as the number 12. |
| 8 | 270,000 | Year two is 25 percent above year one, and year three multiplies 300,000 by 0.9 to give 270,000. |
| 9 | 5 fewer, falling from 17 to 12 | Changing the criterion from 60 to 70 takes the count from 17 to 12, a drop of 5, which two COUNTIFS calls make plain. |
| 10 | Wrong: a mean above the median only signals right skew, and usually fewer than half sit above the mean | A few very high scores lift the mean, so to learn how many students beat it you must count them with COUNTIF rather than compare the mean with the median. |
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.