IFERROR and ISERROR are both functions in Excel that help identify and address error messages like #DIV/0!, #N/A and #REF!. The reason you would use either function is to show an alternate answer to the the error message that Excel generates, normally because you don’t want it to affect other calculations. However there is an important difference between the two and when you would use them. So what is the difference between IFERROR and ISERROR?
Table of contents
IFERROR- The New Function on the block
IFERROR is a fairly recent formula and is a result of Excel users being frustrated at having to continually combine an ISERROR with an IF statement to address issues where they know that they are ‘acceptable’ errors.
So if I compare year on year movement and the one year is zero, I know that I will get an #DIV/0! message, but I would prefer a 0% or a – .
Below in 2015 we had no interest but in 2016 we had interest. The growth is correctly shown as a division by zero error but that doesn’t help us when we try to calculate an average growth.
This is a perfect case for IFERROR. We want Excel to only consider what the alternate is if there is an error. If there is no error it must just use the result of the calculation.
To see how to use IFERROR, look at the post on IFERROR and VLOOKUP.
ISERROR- Alternate now available but still useful
Prior to IFERROR, you needed to create an IF with an ISERROR in it to test if a result was an error message, and then tell Excel what to do if there was an error AND what to do if there wasn’t an error.
Although this is a slightly longer formula, it has the benefit that you can specify what must happen in a TRUE (it is an error) and FALSE (not an error) situation. Whereas IFERROR assumes that you always want the result if it isn’t an error, ISERROR allows you to specify whether you want the result or something else.
So in the IFERROR example above, we are happy to see the growth rate if it is not an error.
In the example below, we want to see which students owe us money. In this case it is less about the amount and more about whether they owe us anything or not. So instead of seeing an amount or a #N/A, we want to see the words OUTSTANDING or ZERO.
IFERROR would not allow this. You can specify the ZERO part, but it will automatically use the amount, as you don’t have an option of what it should do if it is not an error.
This is where ISERROR is useful.
The ISERROR function simply returns a TRUE or FALSE if the cell is or isn’t an error. Once you have the TRUE or FALSE, you can tell Excel what to do in each case using an IF function.
As shown below, we have wrapped the VLOOKUP in an ISERROR and included it in an IF.
Related
ISERROR in Excel (YouTube link)
How to safely delete sheets so there is no REF errors
Finding the IFERROR, ISERROR, ISERR, ISNA, ISREF, IFNA errors
Tracing DIV and Value errors to their source