Troubleshooting formulas
- By Paul McFedries
- 4/3/2019
- Understanding Excel’s error values
- Fixing other formula errors
- Handling formula errors with IFERROR()
- Using the formula error checker
- Auditing a worksheet
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, "")
NOTE
If you want to handle the specific case of the #N/A error, use the IFNA(value, value_if_na) function. Here, value is the expression that you’re testing for the #N/A error, and value_if_na is the value to return if value returns the #N/A error.