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
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
As you see in Figure 30-2, when this formula is copied down the column, the result is a bit more