Learning Excel: IFERROR
Catching error values
Dave wants to know what each member pays per visit. For Sara, a strange code appears. "Is the sheet broken?" No, it's telling you something.
An error value
When Excel can't work something out, it shows an error value. In G11, =E11/D11 divides Sara's amount by her number of visits. That's 0, and you can't divide by 0: #DIV/0!.
An error value isn't a disaster. It's Excel's way of telling you where the problem is.
=E11/D11
| 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 | #DIV/0! | |
| 12 |
The best-known errors
#DIV/0!: dividing by 0 or by an empty cell. #VALUE!: calculating with text. #NAME?: a name or function Excel doesn't know, often a typo. #N/A: something that was looked up wasn't found.
With IFERROR you catch an error: =IFERROR(E11/D11,"-") shows the result, or a dash if there's an error.
Try it yourself
In G2, put what Jack pays per visit: the amount divided by his visits. Show a dash "-" if that gives an error. Fill down to G11.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 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