Safe division
| A | B | |
|---|---|---|
| 1 | Revenue | Units |
| 2 | 10 | 0 |
Formula: D2: =IFERROR(A2/B2,0)
Result: 0
Dividing by zero gives #DIV/0!, which IFERROR replaces with 0.
Logic
IFERROR
Replaces any error.
Syntax
IFERROR(value, value_if_error)Example: =IFERROR(B2/C2,0)
Available in: All versions
IFERROR returns a replacement value when a formula produces any error, and the formula result otherwise: =IFERROR(value, value_if_error). It is the quickest way to hide #N/A, #DIV/0! and similar errors in reports.
When to use it
- A lookup may legitimately fail and you want "Not found" instead of #N/A.
- A division could hit zero and you want 0 or a blank.
- You summarise data that may be empty, such as an average of an empty range.
- You are cleaning a report before sharing it.
Arguments explained
- value. The formula or expression to evaluate. If it returns any error (#N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! or #NULL!), the second argument is used instead.
- value_if_error. What to show when value is an error: text in quotes, a number such as 0, an empty string "" for a blank look, or even another formula.
- Caution. IFERROR hides every error type, including real mistakes in your formula. When you only expect missing matches, IFNA is the safer choice.
Worked examples
Friendly lookup failure
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | A101 | 24.99 |
| 3 | A102 | 39 |
Formula: D2: =IFERROR(VLOOKUP("Z9",A2:B3,2,FALSE),"Not found")
Result: Not found
Z9 is not in the list, so VLOOKUP returns #N/A and IFERROR shows the text.
Average of an empty range
| A | |
|---|---|
| 1 | Notes |
| 2 | n/a |
| 3 | pending |
Formula: D1: =IFERROR(AVERAGE(B2:B4),"No data")
Result: No data
AVERAGE of cells with no numbers gives #DIV/0!, replaced by "No data".
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =IFERROR(B3/D3,"n/a")
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | Extra credit | Attempts | Result | |
| 2 | Ann | 92 | Yes | 2 | n/a | |
| 3 | Bob | 74 | No | 0 | ||
| 4 | Cy | 58 | Yes | 3 | ||
| 5 | Dee | 81 | No | 1 | ||
| 6 | Eli | 66 | 2 |
Result in F2: n/a
Common errors and how to fix them
- Hides real mistakes. IFERROR catches every error, including typos in references. Wrap only the part that may fail, and test the unwrapped formula first.
- #N/A only. To catch only #N/A and let other errors show, use IFNA.
- Wrong value shown as result. A replacement of 0 can be mistaken for real data in totals. Use "" or a label if zeros are meaningful.
- Slow workbooks. Wrapping heavy formulas evaluates them twice in older versions. Use LET to calculate once.
Related functions
Frequently asked questions
- What is the difference between IFERROR and IFNA?
- IFERROR catches all error types. IFNA catches only #N/A, so genuine mistakes stay visible.
- Does IFERROR work with arrays?
- Yes, in Microsoft 365 it works across spilled results.
- Should I wrap every formula in IFERROR?
- No. Hiding errors can mask broken references. Use it where a failure is expected.
- Can IFERROR return a blank?
- Yes: =IFERROR(A1/B1,"").