How to use ISERROR and IFERROR to solve your problems

How to use ISERROR and IFERROR

When working in Excel, sometimes we will experience errors.
iserror-iferror
In this tutorial, we will look at how to use ‘IFERROR’ and ‘ISERROR’ in Excel in order to deal with these errors constructively.

All of the following examples will be performed using Excel 2016 for Windows.

Follow along by downloading our sample file right below!

Download This Tutorial’s FREE Sample File

The ‘ISERROR’ Function

The first function we want to take a look at is ‘ISERROR’.

The syntax is simple and only requires a single argument.

‘=ISERROR(value)’

The ‘value’ is simply what we are checking for an error.

Most often, and for this tutorial, the ‘value’ argument is a cell reference.

Errors we are testing for

So what are the errors we are testing for using the ‘ISERROR’ function?

It tests for #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.

  • So if our ‘value’ argument is any of these errors, the ‘ISERROR’ function will return the result ‘TRUE’.
  • Conversely, if the ‘value’ argument is not any of these errors, the function will return a ‘FALSE’ result.

This can be very useful when we would rather substitute another value in cells that would throw an error for some reason.

Let’s take a look at an example for this to make a bit more sense!
Kasper Langmann, Co-founder of Spreadsheeto

In this scenario, we will say that we have a data set consisting of a column of numerators and a column of denominators.

Then we have a column that contains the result of dividing the numerator by the denominator.

There are some zeros in the denominator column which will cause a divide by zero, or ‘#DIV/0!’, error for those rows in the ‘Result’ column.

If we use the ‘ISERROR’ function in our ‘Result’ column we will get either a ‘TRUE’ or ‘FALSE’ value indicating which rows do or do not contain an error.

Note: That the ‘#DIV/0!’ errors have now been replaced by ‘TRUE’.

We could go a step further and make Excel place a zero in those rows to replace the error for the sake of a cleaner look to our data while not interfering with the calculations on all other rows.

In order to do this, we could nest our ‘ISERROR’ function within and ‘IF’ function.
Kasper Langmann, Co-founder of Spreadsheeto

For anyone not familiar with the ‘IF’ function, it simply consists of a logical test (in this case, our ‘ISERROR’ function) and the value we choose for it to return for either the true or false result of that logical test.

‘=IF(logical_test, [value_if_true], [value_if_false])’

So where the formula in cell C2 is currently ‘=A2/B2’, we will now substitute a new formula:

‘=IF(ISERROR(A2/B2),0,A2/B2)’

What this formula actually says that if our ‘ISERROR’ function returns ‘TRUE’, return the value ‘0’ but if it is false, return the result of the original mathematical calculation.

Note that even though we have selected the number zero for our ‘value_if_true’ argument in the ‘IF’ function, we could also select a string value or even a cell reference that contained a value we wanted the formula to return.
Kasper Langmann, Co-founder of Spreadsheeto

While this method is a great way to substitute another value when an error occurs in a cell, Excel actually offers a more streamlined, efficient function for this exact issue.

The ‘IFERROR’ function

Where the ‘ISERROR’ function simply returns a TRUE or FALSE value to indicate the presence of an error, the ‘IFERROR’ function provides us a method by which we can dictate further action based on either of those two outcomes.

As mentioned before, it is essentially a function that condenses the same concept of nesting the ‘ISERROR’ within an ‘IF’ function.

The syntax of IFERROR

Let’s look at the syntax.

‘=IFERROR(value, value_if_error)’

This function has two arguments:

  • ‘value’
  • ‘value_if_error’

In a nutshell, the function simply says that if the ‘value’ argument is an error, then return the ‘value_if_error’.

If the ‘value’ argument is not an error, the function will return that value.

Compared to nesting the ‘ISERROR’ function in an ‘IF’ function, it’s plain to see how ‘IFERROR’ is far more efficient.

Notice how our new formula to perform the same action as our previous formula eliminates a few steps.
Kasper Langmann, Co-founder of Spreadsheeto

Now we can accomplish the same thing we did with:

‘=IF(ISERROR(A2/B2),0,A2/B2)’
with
‘IFERROR(A2/B2,0)’

‘IFERROR’ eliminates a lot of the extra that was necessary in our previous nested formula and it simply says that if our ‘value’ argument (‘A2/B2’) results in an error, return the ‘value_if_error’, or ‘0’.
Kasper Langmann, Co-founder of Spreadsheeto

It’s that simple! 🙂

The function itself implies that if the ‘value’ is not an error, simply return the ‘value’ argument as a result.

Exploring further possibilities with ‘IFERROR’

The ‘IFERROR’ can also be used to help with flagging data for further inspection or validation.

Often times when using more complicated formulas like the ‘VLOOKUP’, there can be various causes of errors.

Without digging into the nuts and bolts of the ‘VLOOKUP’ function, the important thing to note is that there are different possible errors; ‘#N/A’, ‘#REF!’, ‘#VALUE!’, and ‘#NAME?’. ‘IFERROR’ will handle them all.
Kasper Langmann, Co-founder of Spreadsheeto

Sometimes more complex formulas are built on so many component functions that ‘upstream’ errors have subsequent ‘downstream’ effects and pinpointing the root cause can be a challenge.

Consider the following extremely complex formula:

=IFERROR(SUM(INDIRECT(“‘”&”2016 year to date”&”‘!”&ADDRESS(MATCH($A4,’2016 year to date’!$A:$A,0),3)):INDIRECT(“‘”&”2016 year to date”&”‘!”&ADDRESS(MATCH($A4,’2016 year to date’!$A:$A,0),COUNTIF(‘2016 year to date’!$1:$1,”<“&TODAY())+2))),””)

We could easily say this is one of the ugliest formulas we have seen but we really don’t know the half of it until we begin to consider all the possible causes for errors.

There are so many pieces to this formula where errors could be lurking.

And while this specific example only uses the ‘IFERROR’ to trap any error within the entire formula, we could easily contain different sections of the formula within its own ‘IFERROR’ function to allow us to flag those sections with a different ‘value_if_error’.
Kasper Langmann, Co-founder of Spreadsheeto

No doubt, this would only serve to increase the existing formula’s complexity, but doing so can make troubleshooting far more efficient and effective.

The takeaway here is that this is an extreme example that emphasizes the usefulness of the ‘IFERROR’ function.

Just remember that  ‘IFERROR’ is a logical function based on the TRUE or FALSE outcome model that allows us to isolate errors and choose our ‘value_if_error’ accordingly to best handle errors and troubleshoot them appropriately.
Kasper Langmann, Co-founder of Spreadsheeto

This alone makes the ‘IFERROR’ function one of the most fundamental tools any Excel user should know how to use!

CLICK HERE to try our free Excel training.

2017-02-28T08:45:47+00:00

Send this to a friend