Learning Excel: IFS
More than two choices
The summer pass gets three prices: one for children, one for seniors from 65 and one for everyone else. "But IF only chooses between two," sighs Dave. Not if you nest them.
IF inside IF
The third argument of IF can itself be another IF. That way you test one thing after the other: first whether someone is a child, and if not, whether they're a senior.
=IF(B2<12,"child",IF(B2>=65,"senior","adult")) gives three possible outcomes. Count the brackets: each IF closes its own bracket.
=IF(B2<12,"child",IF(B2>=65,"senior","adult"))
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Age | Pass | Visits | Paid | Category | Per visit |
| 2 | Jack | 9 | Single | 3 | £15.00 | child | |
| 3 | Nora | 34 | Annual | 22 | £240.00 | ||
| 4 | Mila | 15 | Monthly | 9 | £25.00 | ||
| 5 | Rita | 67 | Annual | 15 | £240.00 | ||
| 6 | Laura | 41 | Single | £10.00 | |||
| 7 | Rory | 7 | Monthly | 6 | £25.00 | ||
| 8 | Jim | 72 | Annual | 18 | £240.00 | ||
| 9 | Will | 28 | Single | 4 | £20.00 | ||
| 10 | Hannah | 12 | Monthly | 11 | £25.00 | ||
| 11 | Sara | 55 | Annual | 0 | £240.00 | ||
| 12 |
IFS
With lots of choices, that turns into a tangle of brackets. IFS puts them in a row: a test, an outcome, a test, an outcome…
=IFS(B2<12,"child",B2>=65,"senior",TRUE,"adult") does the same. Excel takes the first test that is true. The last test is simply TRUE: it's always true, so it catches everyone else.
Try it yourself
In F2, give each member their category: child (under 12), senior (65 or over) or adult. Fill down to F11.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Age | Pass | Visits | Paid | Category | Per visit |
| 2 | Jack | 9 | Single | 3 | £15.00 | ||
| 3 | Nora | 34 | Annual | 22 | £240.00 | ||
| 4 | Mila | 15 | Monthly | 9 | £25.00 | ||
| 5 | Rita | 67 | Annual | 15 | £240.00 | ||
| 6 | Laura | 41 | Single | £10.00 | |||
| 7 | Rory | 7 | Monthly | 6 | £25.00 | ||
| 8 | Jim | 72 | Annual | 18 | £240.00 | ||
| 9 | Will | 28 | Single | 4 | £20.00 | ||
| 10 | Hannah | 12 | Monthly | 11 | £25.00 | ||
| 11 | Sara | 55 | Annual | 0 | £240.00 | ||
| 12 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson