Microsoft Office Tutorials and References

In Depth Information

**Avoiding Error Displays in Formulas**

Avoiding Error Displays in Formulas

Sometimes a formula returns an error, such as #REF! or #DIV/0!. Usually, you want to know when

a formula error occurs, but in some cases you might prefer to simply avoid displaying the error

messages. Figure 85-1 shows an example.

Figure 85-1:
The formulas in column D display an error if the data is missing.

Column D contains formulas that calculate the average sales volume. For example, cell D2

contains this formula:

=B2/C2

Using the ISERROR function

As you can see, the formula displays an error if the cells used in the calculation are empty. If you

prefer to hide those error values, you can do so by using an IF function to check for an error.

For this example, change the formula in cell D1 to

=IF(ISERROR(B2/C2),””,B2/C2)

The ISERROR function returns TRUE if its argument evaluates to an error. In such a case, the IF

function returns an empty string. Otherwise, the IF function returns the calculated value. As you

see in Figure 85-2, when this formula is copied down the column, the result is a bit more visually

pleasing.