Error Handling Functions in Microsoft Excel

0
164

In Microsoft Excel we have different reasons to occur different errors while performing the calculation. Error messages are bad especially when it appears in dashboards, or user reports. Here you will learn about error handling functions in Microsoft Excel.

We get 9 type of errors in Microsoft Excel: –  

Error Reason
#DIV/0 This error occurs when denominator value is 0 or blank
#NULL!  This error occurs when we insert a space instead of a comma (the union operator) between ranges used in function arguments
#N/A This error occurs when we use lookup formula to check that value is available or not
#NAME? When we enter wrong formula name
#NUM! When we enter non-numerical value where formula expects a numeric value then formula returns #N/A error
#REF! When we delete referred cell from the data or it could be other reasons like cell has been overwritten by other cell
#VALUE! When we enter invalid value or wrong number in argument to a function could be a reason of #VALUE! Error
######## Column is not wide enough to accommodate a large value
Circular Reference when the calculating cell defined as part of calculation in function

 

We have few of Excel Error Handling Functions:-

  1. ISNA Function
  2. IFNA Function
  3. ISERROR Function
  4. IFERROR Function

What ISNA Function does?

ISNA function use to check if a value is #N/A error and returns a TRUE Boolean and if the input is not #N/A error then it will return as FALSE.

Syntax & Arguments:

=ISNA(value)

value – To check the cell value if #N/A

Example: –

In our data we will check #N/A error.

Excel ISNA Function
Excel ISNA Function

Note: –  #N/A is not an error, it is a special value that we can enter manually in a cell to indicate that we do not have a necessary value.

What IFNA Function does?

IFNA function returns the specified value as result at the place of #N/A error else returns the result of the formula.

Syntax & Arguments:

=IFNA(value, value_if_na)

value – Required argument to check for #N/A error value

value_if_na –  This argument is required to evaluate the value to the #N/A error value

Example: –In our data we have used VLOOKUP function, and as result we are getting #N/A error. Print “Not Available” at the place of error.

Excel IFNA Function
Excel IFNA Function
  • Enter IFNA function in cell C9
  • =IFNA(B9,”Not Available”)
Example - IFNA Function
Example – IFNA Function

We can use IFNA function while looking up for the data. Whenever VLOOKUP function will return #N/A error IFNA will help to specify the string “Not Found” in the cell.

What ISERROR Function does?

ISERROR function we use to check if the cell contains any error message, including #N/A, #VALUE! , #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!

Syntax & Arguments:

=ISERROR(value)

value – Required to check error is available or not

Example: –

In this example we have taken all errors which occurs in Excel as an error.

Excel ISERROR Function
Excel ISERROR Function
  • Enter function in cell B9
  • =ISERROR(A9), copy in below range from B10:B14

    Excel ISERROR Function
    Excel ISERROR Function

What IFERROR Function does?

IFERROR function we use to return specify value if a formula evaluate to an error otherwise it returns same result of the formula.  IFERROR function trap error and display a friendly result.

Syntax & Arguments:

=IFERROR(value,value_if_error)

value – Required to check error is available or not

value_if_error –  This argument is required to evaluate the value to the error value

Example: –

We have data in which we have numerator and denominator, there are 2 numerators in which one numerator is 0.

Excel IFERROR Function
Excel IFERROR Function
  • Result after Dividing numerator with denominator, Error Handling Functions

Error Handling Functions

  • Now, we will enter same formula along with IFERROR function
  • Enter =IFERROR((A9/B9),””) this formula in cell D9
  • Copy same formula in below range D10:D14
Example - IFERROR Function
Example – IFERROR Function

At the place of error, we have specified blank (“”), this is the reason where calculation is returning error IFERROR function is showing result as blank.

Note: – IFERROR function is more efficient in comparison to ISERROR function.

Related Articles

Error Types | How to Solve Errors

LEAVE A REPLY

Please enter your comment!
Please enter your name here