IFERROR: showing a message instead of an error
IFERROR works out a formula and, if the answer is an error such as #DIV/0! or #N/A, shows something else instead: a blank, a zero or a short message.
A worked example
Each sales rep's average order is their sales divided by their number of orders.
- Grace sold £1,200 from 8 orders. £1,200 ÷ 8 = £150.
- Tom sold £540 from 6 orders. £540 ÷ 6 = £90.
- Priya is new and has no orders yet. £0 ÷ 0 can't be worked out, so Excel shows #DIV/0!.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | Orders | Average |
| 2 | Grace | £1,200.00 | 8 | £150.00 |
| 3 | Tom | £540.00 | 6 | £90.00 |
| 4 | Priya | £0.00 | 0 | #DIV/0! |
The error is correct, but it looks broken, and it breaks any total that includes it. It would be clearer to show None.
The formula
=IFERROR(B2/C2,"None")
B2/C2is the formula to try."None"is what to show if it gives an error. Use""for a blank or0for zero.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | Orders | Average |
| 2 | Grace | £1,200.00 | 8 | £150.00 |
| 3 | Tom | £540.00 | 6 | £90.00 |
| 4 | Priya | £0.00 | 0 | None |
With lookups
IFERROR is often wrapped round a VLOOKUP to show a message when the value isn't on the list:
=IFERROR(VLOOKUP(A2,$E$2:$G$6,3,FALSE),"Not found")
XLOOKUP has this built in as its fourth part, so it doesn't need IFERROR.
When not to use it
IFERROR hides every error, including real mistakes. If you misspell a function or point at the wrong range, IFERROR shows your friendly message and you never find out. So:
- Get the formula working first, then add IFERROR.
- For lookups,
IFNAis safer. It only catches #N/A ("not found") and still shows other errors:=IFNA(VLOOKUP(A2,$E$2:$G$6,3,FALSE),"Not found"). - If you know exactly what causes the error, an IF can be clearer:
=IF(C2=0,"None",B2/C2).
Common mistakes
Putting the message in the wrong place
The formula comes first and the message second. =IFERROR("None",B2/C2) just shows None everywhere.
Text without quotes
=IFERROR(B2/C2,None) gives #NAME? on the rows that had an error, because Excel doesn't know anything called None. Put the message in double quotes.
Using "" and then adding up
A blank from "" is text. SUM ignores it, but a formula like =D2+D3 gives #VALUE!. Show 0 if the cells will be added together.
Try it now
In D2, work out each rep's average order (sales ÷ orders). Show None instead of an error for reps with no orders. Your formula will be copied down to D7.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | Orders | Average |
| 2 | Grace | £1,200.00 | 8 | |
| 3 | Tom | £540.00 | 6 | |
| 4 | Priya | £0.00 | 0 | |
| 5 | Marek | £2,250.00 | 9 | |
| 6 | Nia | £0.00 | 0 | |
| 7 | Ollie | £980.00 | 7 |
Where it works
IFERROR works in Excel 2007 onwards, Google Sheets and LibreOffice Calc. IFNA works in Excel 2013 onwards and the others.