1 - Introduction ~5 mins
Welcome
A long list of journey minutes is hard to act on. Three clear bands turn that list into something a student council can use.
By the end of this lesson you will:
- Band a number column with nested IF and IFS
- Flag rows with AND, then compare with OR
- Count bands with COUNTIF and hide empty-group errors with IFERROR
Warm-up
Would 24 raw minute values help a reader decide, or would three bands be easier?
2 - Key Concepts ~5 mins
| Concept | Why it matters | Example |
|---|---|---|
| Nested IF / IFS: tests in order for three or more labels | One formula gives every journey a band | Under 15, 15 to 30, Over 30 |
| AND / OR: AND needs every test true; OR needs any one | Flags rows that deserve a second look | Long journey AND Would_cycle Yes |
| $ lock: $ keeps a range fixed when you fill | COUNTIF still scans the full band list | $H$2:$H$25 |
| COUNTIF: tallies cells that match a label | Band labels become a summary | How many Under 15 |
| IFERROR: your message when a formula errors | Empty groups stay clean on a shared sheet | No matching rows → No answers |
Order matters: test the narrow band first. In IFS, put TRUE last for everything else.
3 - Step-by-step Task: Bands, Flags and Clean Counts ~25 mins
Build journey-time bands with nested IF and IFS, AND and OR flags, tidy band counts and one clean empty-group message. Copy the sample table below, then follow the steps.
Sample data (copy the whole table, then paste at A1):
| Response | Class | Transport | Minutes | Cost_per_week | Would_cycle | Journey_rating |
|---|---|---|---|---|---|---|
| 1 | TY1 | Bus | 25 | 12 | Maybe | 3 |
| 2 | TY1 | Walk | 10 | 0 | No | 4 |
| 3 | TY1 | Car | 15 | 0 | Yes | 3 |
| 4 | TY1 | Bus | 40 | 15 | No | 2 |
| 5 | TY1 | Cycle | 12 | 0 | Yes | 5 |
| 6 | TY1 | Walk | 8 | 0 | No | 5 |
| 7 | TY1 | Train | 45 | 25 | No | 2 |
| 8 | TY1 | Car | 20 | 0 | Maybe | 3 |
| 9 | TY2 | Bus | 30 | 12 | Yes | 2 |
| 10 | TY2 | Walk | 15 | 0 | Maybe | 4 |
| 11 | TY2 | Bus | 35 | 15 | No | 2 |
| 12 | TY2 | Car | 10 | 0 | Yes | 4 |
| 13 | TY2 | Cycle | 18 | 0 | Yes | 4 |
| 14 | TY2 | Bus | 20 | 10 | Maybe | 3 |
| 15 | TY2 | Car | 25 | 0 | No | 3 |
| 16 | TY2 | Walk | 5 | 0 | No | 5 |
| 17 | TY3 | Bus | 50 | 15 | No | 1 |
| 18 | TY3 | Train | 40 | 20 | Maybe | 2 |
| 19 | TY3 | Car | 12 | 0 | Yes | 4 |
| 20 | TY3 | Walk | 20 | 0 | Maybe | 3 |
| 21 | TY3 | Bus | 28 | 12 | Yes | 3 |
| 22 | TY3 | Cycle | 15 | 0 | Yes | 5 |
| 23 | TY3 | Car | 30 | 0 | Maybe | 2 |
| 24 | TY3 | Bus | 22 | 10 | No | 3 |
4 - Common Issues ~3 mins
Common Issues
| Issue | Solution |
|---|---|
| Every row lands in the last band | Test Under 15 first, then 15 to 30, then the rest |
| AND stays OK instead of Review | Match Would_cycle text exactly, capital Y in Yes |
| #DIV/0! on an empty group | Wrap AVERAGEIF or a divide in IFERROR with plain text |
| IFS shows #N/A | Put TRUE last; commas between each pair |
| Band counts do not add to 24 | Match H spelling; keep COUNTIF on $H$2:$H$25 |
5 - Portfolio Build: 05_bands ~15 mins
Independent Practice
- Open {{code:02_survey_analysis}} and add sheet {{code:05_bands}}.
- Own cleaned data from A1, or paste the lesson sample and type Sample used at the top.
- Band one number column with a nested IF or IFS.
- One AND or OR flag on two real headers.
- COUNTIF summary for each band.
- Wrap one empty-group average or divide in IFERROR so the cell shows plain text, not #DIV/0!.
- Sheet {{code:05_bands}} sits inside {{code:02_survey_analysis}}
- Every answer to one number question shows a clear band label
- A flag column marks rows that meet two conditions with AND or OR
- Band counts are visible and no error codes appear on the sheet
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.