1 - Introduction ~5 mins
Welcome
Raw counts mislead when groups differ in size. Today lock percentages to each total, rank both groups, and colour-scale the shares.
- Locked percentages, RANK.EQ and colour scales on two groups
- One sentence stating what the comparison shows
Warm-up
TY1: 2 bus of 8. TY2: 3 bus of 12. Who uses the bus more? Both are 25%: raw counts made TY2 look bigger.
Get ready
Open {{code:02_survey_analysis}} in {{code:Digital_Spreadsheets}}. The practice runs on a sample you paste in the next task.
2 - Key Concepts ~5 mins
| Concept | Why it matters | Example |
|---|---|---|
| Percentage of a total: count ÷ group total | Fair compare when sizes differ | 2 bus users of 8 is 25% |
| Absolute reference: $ locks a cell on fill-down | One % formula fills a whole column | Lock the total: B2/$B$8, then fill so every row still divides by B8 |
| RANK.EQ and colour scale: order and paint high to low | Eye finds top and bottom; ties share a rank | Three 25% shares all rank 1; next is 4 |
COUNTIFS and ROUND appear as short callouts when you type those formulas in the build.
3 - Step-by-step Task: Build a Two-group Comparison ~25 mins
Build sheet {{code:practice_compare}} on the sample journey data. Our sample groups are both 8, so counts are easy to check. The same % method is what you use when totals differ, as in the warm-up.
Block A (~15 min): counts, totals, locked rounded %, percent format, then stop at the checkpoint. Block B (~10 min): RANK.EQ, colour scales on both groups, one finding sentence. Your real portfolio sheet {{code:04_compare}} comes later.
RANK.EQ: first argument = the %, second = the % list locked with $, third = 0 means largest = rank 1. Ties share a rank.
First, the sample: add a sheet named {{code:sample_compare}}, select {{cell:A1}} and paste the whole table below (headers in row 1, data in rows 2–17). The practice formulas read this sheet, so your numbers match the checks whatever your own survey holds.
Sample columns: Response, Class, Transport, Minutes, Cost_per_week, Would_cycle, Journey_rating.
| Response | Class | Transport | Minutes | Cost_per_week | Would_cycle | Journey_rating |
|---|---|---|---|---|---|---|
| 1 | TY1 | Bus | 25 | 12 | No | 3 |
| 2 | TY1 | Walk | 15 | 0 | Yes | 4 |
| 3 | TY1 | Car | 20 | 8 | No | 3 |
| 4 | TY1 | Bus | 30 | 12 | No | 2 |
| 5 | TY1 | Walk | 18 | 0 | Yes | 5 |
| 6 | TY1 | Car | 22 | 10 | No | 3 |
| 7 | TY1 | Cycle | 20 | 0 | Yes | 4 |
| 8 | TY1 | Train | 35 | 15 | No | 3 |
| 9 | TY2 | Bus | 28 | 12 | No | 3 |
| 10 | TY2 | Bus | 32 | 12 | No | 2 |
| 11 | TY2 | Walk | 12 | 0 | Yes | 5 |
| 12 | TY2 | Car | 18 | 9 | No | 4 |
| 13 | TY2 | Bus | 26 | 12 | No | 3 |
| 14 | TY2 | Walk | 16 | 0 | Yes | 4 |
| 15 | TY2 | Car | 24 | 10 | No | 3 |
| 16 | TY2 | Cycle | 22 | 0 | Yes | 5 |
Expected counts: TY1 Bus/Walk/Car/Cycle/Train = 2,2,2,1,1 (total 8). TY2 = 3,2,2,1,0 (total 8). TY1 bus 25%; TY2 bus 37.5%.
4 - Common Issues ~2 mins
Common Issues
| Issue | Solution |
|---|---|
| Every % looks huge or identical after fill-down | Edit the first % formula, lock the total with $ (F4 or type $ yourself), fill down again |
| RANK.EQ errors or odd ties | Point at the % column only, use 0 for largest-first; equal shares share a rank |
| Colour scale does nothing | Select the % cells first, then apply the scale to that selection only |
| You see 13% or 38% instead of 12.5% / 37.5% | Select the % range and click Increase Decimal once |
5 - Portfolio Build: 04_compare ~15 mins
Independent Practice
Minimum path: same seven columns as practice. If you have fewer than ten answers, copy the practice layout onto {{code:04_compare}} and write a finding that names your real groups (or the sample groups if that is all you have). The sample still earns portfolio credit when the sheet is complete.
- Sheet {{code:04_compare}} compares two clear groups on one question
- Percentages lock to each group total and stay tidy when a count changes
- Both groups are ranked and colour-scaled
- One sentence under the table states what the comparison shows
Unlock the full learning experience
You're previewing this lesson. Get full access to this lesson and hundreds more — each one ready to teach, with interactive activities, printable resources and pupil progress tracking built in.
This lesson is copyright of Coding Ireland 2017 - 2025. Unauthorised use, copying or distribution is not allowed.