Rational
Replace the formula result (#DIV, #VALUE!, #REF!, #NAME?) with text, blank or Zero
Description
The Excel IFERROR function evaluates a given criterion (value) in case the return value in a cell is an error then it substitutes with a different value. So, if the final result value is a #DIV, #VALUE!, #REF!, #NAME?, #NUM! the expression then you can add the IFERROR function to change the result.
The IFERROR function is for example =IFERROR(2/0,”Error”) will return “Error” as division 2/0 return #DIV/0! error. THE IFERROR function checks the result then removes it from another value.
Syntax
= IFERROR(value, value_if_error)
Parameters
value – The criteria to evaluate.
value_if_error [optional] – A text string if criteria return an error.
Return Value
The text string in case of error otherwise result of criteria (value)
Key Notes
- If the condition (value) after evaluating returns an error, only then the second argument trigger and return the text string given in the second argument [value_if_error].
- The IFERROR function is useful to avoid any error value to display which may occur while evaluating other calculations or formulas.
- If Value_if_erroris an empty cell, IFERROR treats it as an empty string value (“”).
- A more robust way to manage errors in Excel compared to the IFNA
- You can also find an error code corresponding to the error value.
If you want to change the final expression result to Zero or Blank or a String instead of errors like #DIV, #VALUE!, #REF!, #NAME?, #NUM! use IFERROR as shown in our example image. Go ahead and download the workbook.
Keep Reading Similar Functions:
IF
The IF function is a handy logical operator that return value based on a valid test of criteria, if it matches (TRUE) and another value if it does not.
IFNA
The IFNA function evaluate given criterion (value) in case the return value is a #N/A error then it substitutes with a different text string (value_if_na).
IFS
Rational Check multiple criteria and return the first match. Description The Excel IFS function evaluate multiple criteria at once and return the first TRUE criteria
