Excel is a useful tool for data handling, and it is widely used worldwide. The built-in functions make this application more user-friendly. Such a function is the IFERROR function. This function helps to trap and handles the errors while using a formula(such as dividing something by ‘0’). This function is very useful to an Excel user.
This function works exactly according to its name. It returns a custom result or a value that is specified by the user ‘IF’ an ‘ERROR’ occurs. Otherwise, it returns the result generated by the specified function or formula. This function is used to handle all types of Excel errors including #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE! errors.Syntax:
IFERROR(value, value_if_error)
Here, the value_if_error argument may be NULL or a Null string. Arguments:
- value(Required): This is the value, function, or reference that will be checked by the IFERROR function for an error. This argument must be provided by the user.
- value_if_error(Required): This argument is also provided by the user. IFERROR returns this value or text or numbers if an error is found. This argument may be an empty string or null.
Return value: This function returns the contents of the value_if_error argument if an error is found. Otherwise, it returns the standard result of the specified formula or function. Depending on the content of the value_if_error argument, this function may replace the error value with some other values, some text, logical values, or some results of another formula or function. This function returns ‘0’ if an error occurs and the value_if_error parameter is not provided. If the value is an array, this function returns an array of results containing results for each cell.
Example:
- When the value_if_error argument is provided:
| Value 1 | Value 2 | IFERROR function | Result | Remarks |
|---|---|---|---|---|
| 25 | 5 | =IFERROR(A2/B2, “Error”) | 5 | No error occurs here, so the function returns the result “A2/B2”. |
| 35 | 7 | =IFERROR(A3/B3, “Error”) | 5 | No error occurs here, so the function returns the result “A3/B3”. |
| 23 | 0 | =IFERROR(A4/B4, “Error”) | Error | The error occurs while trying to divide by ‘0’. So the function returns the text in the value_if_error argument. |
| N/A | 25 | =IFERROR(A5/B5, “Error”) | Error | An error occurs as value 1 is not valid(N/A). So, again it returns the text in the value_if_error argument. |
| 3 | =IFERROR(A6/B6, “Error”) | 0 | No error occurs here though the value 1 cell is blank. The function accepts this as ‘0’ and returns the result. |
Output:
Basically in this case the function will return zero.
| value 1 | value 2 | IFERROR function | Result | Remarks |
|---|---|---|---|---|
| 25 | 5 | =IFERROR(A2/B2, ) | 5 | No error occurs here, so the function returns the result “A2/B2”. |
| 35 | 7 | =IFERROR(A3/B3, ) | 5 | No error occurs here, so the function returns the result “A3/B3”. |
| 23 | 0 | =IFERROR(A4/B4, ) | 0 | The error occurs while trying to divide by ‘0’. So the function returns ‘0’ as the value_if_error argument is absent. |
| N/A | 25 | =IFERROR(A5/B5, ) | 0 | An error occurs as value 1 is not valid(N/A). So, again it returns ‘0’ as the value_if_error argument is absent. |
| 3 | =IFERROR(A6/B6, ) | 0 | No error occurs here though the value 1 cell is blank. The function accepts this as ‘0’ and returns the result. |
Output:
