Excel IFERROR() Function
The IFERROR() function in excel returns the specified value if initially supplied the argument or formula returns an error, otherwise returns the result of the formula supplied in the first argument. When we use a formula in cells, we might get an error because of the cell value, IFERROR allows us to remove such errors with the values we want.
Syntax
IFERROR(Value,[Value_if_error])
Parameter
Value(required)- This parameter represents the first cell value, which is checked if there is an error.
Value_if_error (optional)- If in the cell there is an error, that error value will be replaced by this value.
Category
Logical
Return
The function returns the specified value if initially supplied the argument or formula returns an error, otherwise returns the result of the formula supplied in the first argument.
Example 1
Objective: Divide Col A by Col B and show the results without using IFERROR and with using IFERROR.
| Col A | Col B | Without IFERROR &Formula | With IFERROR &Formula | ||
| 1 | 22 | 0.0454545 | =A19/B19 | 0.045454545 | =IFERROR(A19/B19,"NA") |
| 121 | 0 | #DIV/0! | =A20/B20 | NA | =IFERROR(A20/B20,"NA") |
| 0 | 0 | #DIV/0! | =A20/B20 | NA | =IFERROR(A21/B21,"NA") |
NOTE: In the above function, on dividing 1 by 0, we get an error. Without using IFERROR, we get #DIV/0! i.e., an error, so using IFERROR, we can give values when we want. You can also enter a number or text, which can let the user know that there is some problem with the data.
Example 2
Objective: Divide COL A by COL B and return "Division Error", if it an produces error; return result otherwise.
| Col A | Col B | Result | Formula |
| 0 | 269 | Error | =IFERROR(C151/B151,"Error") |
| 89 | 85 | 0.95505618 | =IFERROR(C152/B152,"Division Error") |
Example 3
Objective: Divide Value 1 by Value 2 and show the results without using IFERROR and using the IFERROR function.
| Value 1 | Value 2 | Without IFERROR &Formula | With IFERROR &Formula | ||
| 11 | 2 | 22 | =A2*B2 | 22 | =IFERROR(A2*B2,"DATA IS INVALID") |
| 1 | 1 | 1 | =A3*B3 | 1 | =IFERROR(A3*B3,"DATA IS INVALID ") |
| 13 | o | #VALUE! | =A3*B3 | DATA IS INVALID | =IFERROR(A3*B3,"DATA IS INVALID") |
| 25 | n | #VALUE! | =A4*B34 | DATA IS INVALID | =IFERROR(A4*B4,"DATA IS INVALID") |