Home > Sample chapters

Troubleshooting formulas

Handling formula errors with IFERROR()

Earlier you saw how to use the IF() function to avoid a #DIV/0! error by testing the value of the formula divisor to see if it equals 0. This works fine if you can anticipate the specific type of error the user might make. However, in many instances you can’t know the exact nature of the error in advance. For example, the simple formula =GrossProfit/Sales would generate a #DIV/0! error if Sales equals 0. However, it would generate a #NAME? error if the name GrossProfit or the name Sales no longer exists, or it would generate a #REF! error if the cells associated with one or both of GrossProfit and Sales were deleted.

If you want to handle errors gracefully in your worksheets, it’s often best to assume that any error can occur. Fortunately, that doesn’t mean you have to construct complex tests using deeply nested IF() functions that check for every error type (#DIV/0!, #N/A, and so on). Instead, Excel enables you to use a simple test for any error by offering the IFERROR() function:

IFERROR(value, value_if_error)

value

The expression that might generate an error

value_if_error

The value to return if value returns an error

If the value expression doesn’t generate an error, IFERROR() returns the expression result; otherwise, it returns value_if_error (which might be the null string or an error message). Here’s an example:

=IFERROR(GrossProfit / Sales, "")