IFERROR - Handle calculation errors deliberately
Provide a meaningful fallback for a calculation error without confusing an unavailable result with a genuine zero.
By the end, you can
- Wrap a calculation in IFERROR.
- Choose a truthful fallback label.
- Recognize which errors are hidden by a broad guard.
Before you start: Understand division and IF outcomes. The Practice challenge also uses ROUND.
In this lesson
Readable output for an expected error case
Marketing calculates campaign spend per lead. Division is undefined when no leads were recorded. IFERROR returns the normal calculation when it succeeds, or a chosen fallback when it returns an Excel error. It is useful for anticipated missing or invalid calculation results, but it does not prove the source data is correct.
Syntax and arguments
=IFERROR(value, value_if_error)Both arguments are required. The value argument can be a calculation, formula, or cell reference.
| Argument | Purpose | Example |
|---|---|---|
valueRequired | The calculation or value to check. | ROUND(B2/C2,2) |
value_if_errorRequired | The replacement when the first argument produces an error. | "No leads" |
Campaign cost per lead
₹200| A | B | C | D | |
|---|---|---|---|---|
| 1 | Campaign | Spend | Leads | Cost per lead |
| 2 | Search | ₹6,000 | 30 | ₹200 |
| 3 | Display | ₹2,000 | 0 | No leads |
| 4 | ₹1,500 | 14 | ₹107.14 |
=IFERROR(ROUND(B2/C2,2),"No leads")6,000 ÷ 30 is 200, so IFERROR keeps the rounded calculation.
Inspect the error and the non-error cases
=IFERROR(ROUND(B3/C3,2),"No leads")2,000 ÷ 0 produces #DIV/0!, replaced by the chosen label.
=IFERROR(ROUND(B4/C4,2),"No leads")1,500 ÷ 14 is approximately 107.142857; ROUND gives 107.14 and IFERROR preserves it.
=IFERROR(10/0,0)The error becomes zero. Only use this if zero is the agreed meaning, since it can distort subsequent averages.
What the guard hides
- IFERROR catches #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL! results.
- A valid zero, FALSE, or text value is preserved; the fallback is used only for errors.
- It does not repair an invalid formula that Excel cannot enter, or a logically wrong calculation that happens to return a normal number.
- An empty-string fallback looks blank but is still text and is counted by COUNTA. A numeric-zero fallback contributes to COUNT and AVERAGE.
Common mistakes and fixes
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.