Microsoft Office Tutorials and References

In Depth Information

**Tip 30: 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 so you can fix it. But in some cases, you might prefer to simply avoid displaying

the error messages. Figure 30-1 shows an example.

Figure 30-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 IFERROR 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 IFERROR function. This function takes two

arguments: The first argument is the expression that’s checked for an error, and the second is the

value to return if the formula evaluates to an error.

The formula presented earlier can be rewritten as

=IFERROR(B2/C2,””)

As you see in Figure 30-2, when this formula is copied down the column, the result is a bit more

visually pleasing.