Welcome
Today you build a results explorer: pick one option and three figures update at once.
By the end you will:
- Build a drop-down from summary options
- Fetch count, average and percentage with XLOOKUP
- Keep empty cells tidy with IFERROR
Would a busy principal rather see a long table or one box they can change?
Ready: open your cloud storage, open {{code:Digital_Spreadsheets}}, then open {{code:02_survey_analysis}} (or create a new workbook with that exact name). No finished summary yet? Use the on-screen sample in the next task.
| Concept | Why it matters | Example |
|---|---|---|
| Drop-down list: limits a cell to a set list | Stops typing errors on options | Only Bus, Walk, Car, Cycle or Train |
| XLOOKUP: finds a value and returns a match | One formula pulls the right figure | Pick Bus; count shows 8 |
| IFERROR: your text instead of an error | Empty pick stays tidy | Shows Pick an option |
| Results explorer: drop-down plus live figures | Reader tests options fast | Pick Walk and the figures change to a count of 5 and an average of 11.6 minutes |
Formula shape: {{code:XLOOKUP(what_to_find, where_to_look, what_to_return)}} then {{code:IFERROR(that_formula, "Pick an option")}}.
Build a practice explorer on sample transport figures. Paste the summary, add a drop-down, learn plain XLOOKUP, wrap it in IFERROR, then finish the other two figures.
Sample summary (paste at A10; headers in row 10):
| Transport | Count | Average minutes | Percentage |
|---|---|---|---|
| Bus | 8 | 31.3 | 0.333 |
| Walk | 5 | 11.6 | 0.208 |
| Car | 6 | 18.7 | 0.25 |
| Cycle | 3 | 15 | 0.125 |
| Train | 2 | 42.5 | 0.083 |
If paste breaks columns, Undo and type the six rows by hand.
Common Issues
| Issue | Solution |
|---|---|
| Drop-down empty or wrong list | Edit validation; list = options only, no header |
| {{code:#N/A}} on a valid option | Match search range to drop-down list and $ locks |
| Wrong figure for a valid pick | Return letter: $B count, $C average, $D share |
| Share shows 0.33 not 33% | Apply Percentage format to that cell |
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 123Learn.ie 2017 - 2026. Unauthorised use, copying or distribution is not allowed.