中文（繁體）
中文（简体）
English
DSE ICT | Spreadsheets and Data Analysis Lesson-Prep Guide (Tutor Edition) | Formulas, Functions and Chart Choice
Students can press Average but the moment a formula is copied down it breaks. This lesson turns five marks into steps you can check.
Objectives — Students can: (1) use SUM, AVERAGE and COUNTIF to get total, mean and pass rate from raw marks; (2) explain relative versus absolute references ($) when a formula is filled down, and compute a weighted score; (3) choose a column chart, line chart or scatter chart to match the data type and say why the wrong choice distorts the message.
01 Prerequisites (3-minute check)
If any of these fails, fix it before the lesson starts.
| Skill | Quick check | Pass mark |
|---|---|---|
| Arithmetic and percentages | How a weighted mean works | Multiply by weight, then add |
| Cell references | A1 versus $A$1 | Fixed or shifting when copied |
| Function syntax | What comma and colon mean | Comma separates arguments, colon is a range |
02 Lesson flow (60 minutes)
| Time | Stage | Tutor does | Student does |
|---|---|---|---|
| 0-8 min | One raw marks table | Read values, units, blanks | Blank is not zero |
| 8-20 min | Total and mean | SUM, AVERAGE | Total 330, mean 66 |
| 20-30 min | Conditional count | COUNTIF, COUNTIFS | 4 passes, 80% |
| 30-42 min | Absolute reference | Weights in fixed cells with $ | Weighted 68.4 |
| 42-52 min | Lookup and grades | VLOOKUP against a table | Five grades correct |
| 52-60 min | Chart choice | Data type picks the chart | Right chart plus reason |
03 Teaching script (read it out)
Scene 1: Clean the data before the mean
Five students score 62, 75, 48, 88 and 57. SUM gives 330, AVERAGE gives 66. The range 88 – 48 = 40 marks says more about spread than the mean does.
Teach blanks against zeros explicitly: AVERAGE ignores an empty cell but counts a zero, so the same table gives two different answers. This class has no blanks, yet the distinction is examinable.
Cleaning also covers duplicate rows and numbers stored as text: a text-formatted 62 is silently skipped by AVERAGE, so the count of students drops without any error message.
Teach blanks against zeros explicitly: AVERAGE ignores an empty cell but counts a zero, so the same table gives two different answers. This class has no blanks, yet the distinction is examinable.
Cleaning also covers duplicate rows and numbers stored as text: a text-formatted 62 is silently skipped by AVERAGE, so the count of students drops without any error message.
Students must say: why a blank cell and a zero give different means (ignored versus counted).
Scene 2: Conditional counts and weighting are two different operations
COUNTIF takes a quoted condition:
A weighted score multiplies before adding: coursework 72 at 40%, exam 66 at 60%, so 0.4 x 72 + 0.6 x 66 = 68.4, not (72 + 66) / 2 = 69. Write
Drop the $ and the second row points at an empty cell, the column collapses to zero, and nothing on screen reports an error. That silence is what makes it the most common mistake.
=COUNTIF(B2:B6,">=50") gives 4 students, so the pass rate is 4 / 5 = 80%.A weighted score multiplies before adding: coursework 72 at 40%, exam 66 at 60%, so 0.4 x 72 + 0.6 x 66 = 68.4, not (72 + 66) / 2 = 69. Write
=B2*$B$9+C2*$C$9; the $ keeps the weight cells fixed as the formula fills down.Drop the $ and the second row points at an empty cell, the column collapses to zero, and nothing on screen reports an error. That silence is what makes it the most common mistake.
Students must say: why =(B2+C2)/2 is wrong for a weighted score (the papers carry different weights).
Scene 3: Lookup tables and charts: the wrong type distorts
The grade table sits in ascending order (0 to F, 50 to C, 65 to B, 80 to A), so
Charts follow the question: a column chart compares separate individuals; a line chart shows one thing changing over time; a scatter chart shows the relationship between two numbers. A line chart across student names is wrong because names are not a continuous variable, and the line implies a trend that does not exist.
Chart answers need the reason, for example “a column chart compares the totals of five students”. A chart type alone usually earns half marks.
=VLOOKUP(score,table,2,TRUE) gives 62 to C, 48 to F, 57 to C, 75 to B and 88 to A: five out of five. Approximate match needs the first column sorted ascending, or the answer is unreliable.Charts follow the question: a column chart compares separate individuals; a line chart shows one thing changing over time; a scatter chart shows the relationship between two numbers. A line chart across student names is wrong because names are not a continuous variable, and the line implies a trend that does not exist.
Chart answers need the reason, for example “a column chart compares the totals of five students”. A chart type alone usually earns half marks.
Students must say: why comparing five students should not use a line chart (names are not continuous).
04 Question types and worked steps
| Type | Example | Steps | Answer |
|---|---|---|---|
| Formula | Weighted total score | Multiply by weights, fix cells with $ | 68.4 |
| Function | Pass count and rate with COUNTIF | Quote the condition string | 4 students, 80% |
| Lookup | Grade with VLOOKUP | Ascending first column, TRUE match | Five grades |
| Chart | Pick the chart by data type | Compare, trend, relationship | Column chart plus reason |
Sanity check (teach this): Check the total and the count first: 330 / 5 = 66, not divided by four.
A blank cell is ignored by AVERAGE while a zero is counted.
Weighted means multiply then add: 0.4 x 72 + 0.6 x 66 = 68.4; the plain mean 69 is wrong.
VLOOKUP approximate match needs the first column in ascending order.
A blank cell is ignored by AVERAGE while a zero is counted.
Weighted means multiply then add: 0.4 x 72 + 0.6 x 66 = 68.4; the plain mean 69 is wrong.
VLOOKUP approximate match needs the first column in ascending order.
05 Independent practice (3 questions)
| Question | Answer | What to watch |
|---|---|---|
| Mean of 62, 75, 48, 88, 57 | 66 | Total 330 divided by 5 |
| Share scoring 50 or above | 4 students, 80% | COUNTIF then divide by the count |
| Weighted score for 72 at 40% and 66 at 60% | 68.4 | Multiply by weights, then add |
06 Common mistakes and what to say
| Mistake | Student writes | Say this |
|---|---|---|
| Dividing by the wrong count | 330 / 4 = 82.5 | “There are five students, not four. Count the divisor.” |
| Treating weighted as a plain mean | (72 + 66) / 2 = 69 | “The papers carry different weights, so multiply by 40% and 60% first.” |
| Missing $ signs | Weight cells go blank after filling down | “Lock the weights with $B$9, or every row looks in the wrong place.” |
| Wrong chart type | A line chart across five students | “Names are not continuous numbers. Use a column chart to compare.” |
07 Homework (paper, section and question numbers)
- Secondary ICT chapter index — Spreadsheet practice and answers
- All lesson-prep guides — Compare other subjects
08 Exit check (last 5 minutes)
| Question | Answer | Decision |
|---|---|---|
| Mean mark | 66 | All three right: move to charts |
| Pass rate | 80% | One wrong: reteach COUNTIF |
| Weighted score | 68.4 | Two wrong: return to act two |
09 Tutor FAQ
Must students memorise every function?
Papers usually name the allowed functions; the skill is argument order and range writing.
Do blanks and zeros really differ?
Blanks are ignored by AVERAGE and zeros are counted, so the gap grows with class size.
Does a chart answer need a reason?
Yes. Naming the chart without why it fits usually earns half marks.
Find a tutor — freeTutor Registration →
A TutorZone lesson-prep guide. Written by the TutorZone team; questions and lesson flow are original and are not taken from any school or publisher material.
