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")



ADVERTISEMENT
ADVERTISEMENT