中文（繁體）
中文（简体）
English
S2 ICT | Spreadsheet Formulas and Charts: Tutor Lesson-Prep Guide (SUM, AVERAGE, IF, Absolute References)
Students can press SUM, but one drag turns every answer wrong. The problem is not the function, it is the reference that moves with the cell. Five marks are enough to pin down ranges, logic and references in one lesson.
Objectives — Students can: (1) use a range such as A1:A5 to get a total of 370, an average of 74, a maximum of 90 and a minimum of 55 from five marks; (2) write =IF(B2>=60,”Pass”,”Fail”) and name the condition, the true value and the false value; (3) explain that A1 becomes A2 when dragged while $A$1 does not, and say which formulas must be locked.
01 Prerequisites (3-minute check)
If any of these fails, fix it before the lesson starts.
| Skill | Quick check | Pass mark |
|---|---|---|
| Averages | What is the average of 62, 78, 85, 90 and 55? | 74, since the total is 370 and there are five marks |
| Comparison operators | How is greater than or equal to written in a spreadsheet? | >=, with the equals sign second |
| Cell names | What is the cell in row 3, column 2 called? | B3, column letter first, row number second |
02 Lesson flow (45 minutes)
| Time | Stage | Tutor does | Student does |
|---|---|---|---|
| 0-5 min | Estimate first | Write the five marks on the board and ask for the total, the average and how many pass | Totals are usually right, averages often divide by 100 |
| 5-14 min | Ranges and four functions | Type SUM, AVERAGE, MAX and MIN once each and explain the colon in A1:A5 | Type each function and read the result |
| 14-24 min | The three parts of IF | Take =IF(B2>=60,”Pass”,”Fail”) apart and show the role of quotes and commas | Write one IF and name all three parts |
| 24-33 min | Relative versus absolute | Drag one formula two rows to show A1 becoming A2 while $A$1 stays, then multiply by a fixed price | Explain the difference between the two kinds of reference |
| 33-40 min | Four question types | One each on range statistics, IF logic, reference repair and chart choice | All four answered with steps |
| 40-45 min | Exit check | Collect three answers and mark them on the spot | Hand in three answers |
03 Teaching script (read it out)
Scene 1: Act 1: a colon means a stretch of cells, not two
“A1:A5 does not mean two cells, A1 and A5. It means the five cells from A1 to A5. So =SUM(A1:A5) is 62 plus 78 plus 85 plus 90 plus 55, which is 370. The average is 370 divided by 5, which is 74. The usual error is leaving out the last cell: A1:A4 gives four marks, a total of 315 and an average of 78.75. Both figures are wrong and neither looks wrong. Two checks: the function tooltip should show five cells, and SUM divided by COUNT must match. Get the range wrong and every table and chart built on it is wrong.”
A1:A5 is five cells; a short range breaks total and average together.
Scene 2: Act 2: IF has three parts and needs all three
“=IF(B2>=60,”Pass”,”Fail”). Part one is the test, whether B2 is at least 60. Part two is what to show when the test is true. Part three is what to show when it is false. Text goes inside double quotes and the parts are separated by commas. The usual failures are Chinese quotation marks, which make the whole formula an error, and a missing third part. Watch the threshold: >= or > decides the student who scores exactly 60. Test it by typing a mark of exactly 60 and reading the result.”
Test, true value, false value; the threshold decides the 60-mark case.
Scene 3: Act 3: dragging carries the reference with it
“You write =B2*$F$1 in C2 and drag it down to C3. The formula becomes B3 times F1. B2 turning into B3 is exactly what you want, because you are now marking the next student. F1 turning into F2 is a disaster, because F1 holds the fixed price. Anything that must not move is written $F$1: the dollar signs stop the reference moving. After filling a column, click each cell and read the formula bar, asking of every reference whether it should follow the row or stay put.”
Let B2 follow the row and pin the fixed cell as $F$1; check the formula bar after filling.
04 Question types and worked steps
| Type | Example | Steps | Answer |
|---|---|---|---|
| Range statistics | For the marks 62, 78, 85, 90 and 55, write the formulas and results for total, average, maximum and minimum. | (1) Identify the range A1:A5 (2) write each function (3) check the total and average by hand (4) confirm the range includes the last cell | =SUM(A1:A5) gives 370; =AVERAGE gives 74; =MAX gives 90; =MIN gives 55 |
| IF logic | Write a pass or fail formula and state what a mark of 60 and a mark of 59 show. | (1) Fix the threshold and operator (2) write the test (3) write both text values in quotes (4) test the boundary values | =IF(B2>=60,”Pass”,”Fail”); 60 shows Pass and 59 shows Fail |
| Reference repair | A formula =B2*F1 goes wrong when dragged down. Explain why and fix it. | (1) Click the cell and read the formula bar (2) find the reference that moved (3) lock it with dollar signs (4) drag again and check | F1 drifted to F2 and F3; rewrite as =B2*$F$1 and refill |
| Chart choice | To compare five students marks, a column chart or a pie chart? Why? | (1) Decide whether the data compares categories or shows shares (2) choose the chart type (3) justify it (4) state why a pie chart fails here | A column chart compares values; a pie chart shows parts of a whole, and five independent marks do not add up to a meaningful whole |
Sanity check (teach this): Sanity check: an average of 74 must lie between 55 and 90, otherwise the range or the divisor is wrong; four passes plus one fail must equal five students; 370 divided by 100 is 3.7, not 74, so percentages need the 500 total; the spread 90 minus 55 is 35, and 145 means addition instead of subtraction. If any statistic disagrees with hand calculation, check the range before the divisor.
05 Independent practice (3 questions)
| Question | Answer | What to watch |
|---|---|---|
| If all five marks become 70, what are the total and the average, and why is the average unchanged? | Total 350 and average 70, because the sum of five equal values divided by five is that value | Explain the definition of an average rather than recall |
| Write a formula that flags marks above the class average held in B7. | =IF(B2>$B$7,”Above average”,”Not above average”), locking B7 for dragging | Lock the average cell as $B$7 |
| To sort a class list by score and chart it, what are the first two steps? | Select the data range, then insert a column chart with a title and axis labels | Select the range before inserting the chart |
06 Common mistakes and what to say
| Mistake | Student writes | Say this |
|---|---|---|
| A range that misses the last cell | “=SUM(A1:A4) is fine, it is nearly the same.” | “A1:A4 is four marks, so the total is 315 and the average 78.75; both are wrong. Include the last cell and check the tooltip count.” |
| Chinese quotation marks in IF | “=IF(B2>=60, “Pass”, “Fail”) typed with full-width quotes” | “The spreadsheet only accepts straight double quotes; full-width ones break the formula. Retype the quotes and the commas in English.” |
| Dragging without dollar signs | “=B2*F1 just needs to be dragged down.” | “F1 becomes F2, so the fixed price moves. Write $F$1 and it points at the same cell however far you drag.” |
| The wrong chart | “Use a pie chart to compare five marks.” | “A pie chart shows shares of a whole, and independent marks do not form a whole. Compare values with a column chart.” |
07 Homework (paper, section and question numbers)
- Chapter practice: write SUM, AVERAGE, MAX and MIN formulas for the five marks — Spreadsheet chapter, practice question 1
- Chapter practice: write pass and fail IF formulas and test the 60 and 59 boundary — Spreadsheet chapter, practice question 2
- Extension: lock a unit price with dollar signs before dragging and record the formula bar before and after — Spreadsheet chapter, practice question 3 formula column
08 Exit check (last 5 minutes)
| Question | Answer | Decision |
|---|---|---|
| What does =SUM(A1:A5) return and how many cells does the range cover? | 370, over five cells | Wrong means reteach Act 1 next lesson |
| What are the three parts of IF? | The test, the value when true, the value when false | Wrong means reteach Act 2 |
| Why is $F$1 sometimes needed? | It stops the reference moving when the formula is dragged | Wrong means reteach Act 3 |
09 Tutor FAQ
Should VLOOKUP be taught here?
Pin down ranges, IF and references first in S2; VLOOKUP belongs in S3, otherwise students copy it without understanding references.
Must function names be memorised in English?
Yes, function names are always English and exam papers use them; a Chinese interface only shows translations.
How many chart types?
Column, line and pie are enough; the goal is knowing which data each one fits, not collecting chart types.
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.