ISERROR Function
Returns TRUE if the given value is an error and vice versa
Returns TRUE if the given value is an error and vice versa
The ISERROR Excel function is categorized under Information functions. The function will return TRUE if the given value is an error and vice versa. It works on errors – #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL. ISERROR is used in combination with the IF function to identify a potential formula error and display other formulas or text strings in message form or blanks. It can also be used with the IF function to display a custom message or perform some other calculation if an error is found.
In financial analysis, we need to deal with formulas and data. Often, our spreadsheet contains a large number of formulas, so they sometimes don’t work properly and perform calculations as required when an error is encountered. Just like the ISERR function, ISERROR, in combination with the If function, can be used to default a cell’s value when an error is encountered. It allows our formulas to work and evaluate data properly without requiring the user’s intervention.
=ISERROR(value)
The ISERROR function uses the following arguments:
To understand the uses of the ISERROR Excel function, let’s consider a few examples:
Let’s see the results from the function when we provide the following data:
Data | Formula | Result | Remarks |
---|---|---|---|
210 | =ISERR(210) | FALSE | As there is no error, so FALSE |
#REF! | =ISERR(#REF!) | TRUE | TRUE, as it is an error |
#N/A | =ISERR(#NA) | TRUE | Unlike ISERR, it does take the error N/A |
25/0 | =ISERR(25/0) | TRUE | TRUE, as the formula is an error |
#VALUE! | =ISERR(#VALUE!) | TRUE | TRUE, as it is an error |
=ISERR() | FALSE | No error, so FALSE |
If we wish to count the number of cells that contain errors, we can use the ISERROR function, wrapped in the SUMPRODUCT function.
Suppose we are given the following data:
Using the formula =SUMPRODUCT(–ISERROR(B5:C10)), we can get the count of cells with an error, as shown below.
In the above formula:
If we wish to provide a custom message, for example, instead of getting #DIV/0 error, we want the value to be 0. We can use the formula =IF(ISERROR(A4/B4),0,A4/B4) instead of A4/B4.
Suppose we are given the following data:
Instead of TRUE, if we wish to enter the value 0, the formula to use will be =IF(ISERROR(B6),0,B6) and not B6, as shown below:
We get the result below:
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!