Avoiding Excel Error Displays in Formulas

Avoiding Excel Error Displays in Formulas

Avoiding Excel error displays is a fairly simple process and can make your worksheet more attractive. If a formula returns an error whilst you are working on a worksheet, such as #DIV/0! or #REF! then you probably want to know about it and to workout why it s occuring. However, there may be occasions when you don t want to know and wish to avoid the error display, which may be the case if you have a cell which contains a function but the cells used for that calculation are empty.

Let’s take an example of where a formula may return and error due to empty cells.

Column D in the data shown in the image above contains the formula
=B2/C2

As you can see the the average value of sales per item has been calculated for Monday to Thursday because data has been provided for the Total Sales of these days (column B) and the No. of Items (column C). However, for Friday and Saturday the data for Total Sales and No. of Items is missing, meaning that an average cannot be calulated for these months which results in an error being displayed.

You can hide the displayed error by using ISERROR function:

For example:
=IF(ISERROR(B2/C2),"",B2/C2)

If the ISERROR function returns TRUE, meaning yes there is an error, then the IF function causes it to return an empty string. If the function does not return an error then is will return the calculated value.

Below you can see the outcome when the ISERROR function is applied to column D.

Alternatively, the IFERROR function:

The IFERROR function was introduced in Excel 2007, so if your using an earlier version or know that your workbook may be used by people who have an earlier version, then you should stick to the ISERROR function.

Example of the IFERROR function:
=IFERROR(B2/C2,"")

The outcome of the IFERROR function is ultimately the same as ISERROR however easier to write. Also the expression is evaluated one time only and therefore can result in faster recalculation times.