5 Excel Error Functions
In Excel, there are several excel error functions that help you handle and manage errors that may occur in your formulas and calculations. These error functions allow you to control how Excel responds when certain conditions are not met or when errors occur. Here are the main error functions in Excel, along with examples and explanations:
IFERROR Function
It returns a specified value if a formula results in an error; otherwise, it returns the result of the formula.
It detects all types of errors such as #N/A, #VALUE! #REF!, #DIV/0!, #!, #NAME?, #NULL!.
Syntax:=IFERROR(value, value_if_error)
In the above image we can see after dividing the total sales with zero it throws an error. To overcome this we will use Excel IFERROR function. Lets see the below image where in we insert “Total Quantity is Zero” as and when error comes.
ISERR Function
It checks if a value is an error other than #N/A, and returns TRUE if it is, or FALSE if it isn’t. You can use ISERR to identify errors other than #N/A in a cell.
Syntax: =ISERR(value)In the below image we can see when we tried to test ISERR function of excel on #DIV/0! error it shows us TRUE as the excel error is other than #N/A.
ISERROR Function
Checks if a value is any type of error, including #N/A, and returns TRUE if it is, or FALSE if it isn’t. It detects all types errors such as #N/A, #VALUE! #REF!, #DIV/0!, #!, #NAME?, #NULL!.
Syntax: =ISERROR(value)
ERROR.TYPE Function
Returns a number corresponding to the type of error in a cell (e.g., 1 for #NULL!, 2 for #DIV/0!, 3 for #VALUE!, etc.). If any cell contains an error, this formula will return a number corresponding to the error type. It can be useful when multiple error checks are necessary. Number 1 corresponds to #NULL!, 2 to #/0!, 3 to #VALUE!, 4 to #REF!, 5 to #NAME?, 6 to #NUM!, and to #N/A.
Syntax: =ERROR.TYPE(error_val)
IFNA Function
Returns a specified value if a formula results in #N/A error; otherwise, it returns the result of the formula. Similar to IFERROR, IFNA allows you to handle #N/A errors gracefully.
Syntax: =IFNA(value, value_if_na)In the below example we can see we used IFNA function of excel but it throws error as the error we evaluate is not #N/A!.
Please note that your Excel version may impact the availability of these functions.
You can download excel file from below.
You can read more on this from here.
These error functions in Excel are valuable tools for handling and managing errors in your spreadsheets, allowing you to create more robust and user-friendly worksheets and formulas.