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:
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
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.
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.
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.
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
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
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.