IFS with a compound first test
The most specific case first, the general cases after.
Task:
Gym pricing: an adult (18 or over) who is a member pays Member; any other adult pays Adult; everyone under 18 pays Junior. In D2:D7 give each visitor's price band.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of IFS
0/4The same formula in the other shapes it takes at work.
- IFS Function
Use IFS function for multiple conditions without nesting.
CoreAdvanced - IFS with a TRUE catch-all
Without a final TRUE, any value that fits no band comes back as #N/A.
Intermediate - IFS returning calculations
Each branch can work out its own amount.
Advanced - IFS with a compound first test
The most specific case first, the general cases after.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Visitor | Age | Member | Band |
| 2 | V1 | 34 | Yes | |
| 3 | V2 | 17 | Yes | |
| 4 | V3 | 52 | No | |
| 5 | V4 | 18 | No | |
| 6 | V5 | 15 | No | |
| 7 | V6 | 18 | Yes |
What this exercise teachesMay contain the answer
If the adult test came first, members would never reach the Member band — every member is also an adult. With IFS, as with nested IF, the specific cases go before the general ones. V2 is a member but under 18, so the AND fails and they fall through to Junior.