Learning Excel: IF
Choosing with IF
Children under 12 get the summer pass at half price. "Can the sheet tell me for each member whether they're a child?" asks Dave. That's what IF is for.
If this, then that
IF chooses between two outcomes. The first argument is a test, the second is what you get when the test is TRUE, the third what you get when it's FALSE.
F2 holds =IF(B2<12,"child","adult"). Jack is 9, the test is true, so F2 shows "child".
=IF(B2<12,"child","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 |
Text, numbers and calculations
A text outcome goes in quotation marks, a number doesn't. An outcome can also be a calculation.
=IF(C2="Single",5,0) gives £5 for anyone who pays per swim and 0 for the pass holders. The test itself is just a comparison, like in the previous lesson.
Try it yourself
In F2, put "child" for anyone under 12 and "adult" for everyone else. 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