IF and ISERROR VS IFERROR

Apr 1, 2019 | Excel Hints and Tips

It looks like these 2 options are the same or very similar, which they are but each have their pros and cons.

Combining the IF function with the ISERROR function

What is the IF Function?

The IF function on it’s own enables the ability to perform 2 different actions based on a condition.

Structure of IF

=IF(logical test, value if true, value if false)

What is the ISERROR Function?

The ISERROR function is seldom used on it’s own but if it is it gives a value of TRUE if the data is any type of error and FALSE if it isn’t.  Data is viewed to be an error if it has any of the standard error messages such as #NA, #DIV/0, #VALUE etc

Structure of ISERROR

 =ISERROR(value)

Combining the Functions

The functions can be combined by replacing the logical test from the IF function with the ISERROR function instead.  This produces a calculation where error messages can be replaced with something more meaningful.

Structure of IF with ISERROR

=IF(ISERROR(value),value if true, value if false)

Example of IF with ISERROR

Imagine we had a range of cells that we wanted to find the average of but if the data range was empty it would give a #DIV/0 error to show that it is impossible to divide by zero.  Instead we could replace this with the text No Data or maybe even a dash.  Assuming the range of cells we are working with is A1:A10 we could use the following calculation.

=IF(ISERROR(AVERAGE(A1:A10)),”No Data”, AVERAGE(A1:A10))

An image of the IF and ISERROR functions

The biggest advantage of this option is that there are a whole range of other functions which begin with IS which can replace ISERROR to look for things other than errors.  For example, ISBLANK will look for the data being blank, ISTEXT will look for the data being text or ISNUMBER for the data to be a number.

Using the IFERROR function

The IFERROR function was new in Excel 2007 and, as with a lot of the new features, hasn’t been utilised as much as it should have been.  It performs the same calculation as combining IF with ISERROR but is much simpler.

What is the IFERROR function?

The IFERROR function performs the calculation suggested if the result isn’t an error but has an alternative if it is.

Structure of IFERROR

=IFERROR(value, value if error)

Example of IFERROR

Using the same example as earlier the calculation now becomes much shorter

=IFERROR(AVERAGE(A1:A10),”No Data”)

Image of an example of the IFERROR function

You will find that there is often more than one solution using Excel in general or even with calculations.  Neither of these are wrong but the simpler option does minimise the chance of mistakes being made.  On the other hand, the combination of functions does allow for added flexibility.

Want to learn more about Excel then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Excel Courses I run.