IFERROR
Shows your own value instead of an error in a formula: zero instead of #DIV/0!, readable text instead of #N/A.
IFERROR(value, value_if_error)- value
- A formula that may return an error, for example B2/C2.
- value_if_error
- What to show instead of the error: a number, text in quotes or empty quotes "".
Return value
The value of the first argument if it has no error. For any error, whether #N/A, #DIV/0! or #VALUE!, the function returns the second argument.
Examples
Average order value without #DIV/0!
Details
IFERROR first calculates the formula in the first argument. If the result is a normal value, that’s what you see. If it’s any error (#DIV/0!, #N/A, #VALUE! and others), the second argument is shown instead.
Most often it wraps a division where the divisor may turn out to be zero, and a VLOOKUP when the key isn’t in the table.
Keep in mind: the function hides any error, including one caused by a typo in the formula. Make sure the formula is correct first, and only then wrap it.
Common mistakes
- A formula with a typo got wrapped: the error is hidden, and the wrong result looks like an ordinary zero.
- For VLOOKUP, IFNA is safer: it replaces only #N/A and doesn’t hide other errors.
- A zero instead of an error gets into the average and pulls it down. If the row shouldn’t count, return empty quotes "".
In Google Sheets
In Google Sheets, the second argument of IFERROR is optional: without it, you get an empty cell instead of the error.