Check formula errors in Excel 2007 using IFERROR Function
One of the newly introduced functions in Excel 2007 is the IFERROR Function. If you are using a formula in a cell, this function allows you to trap formula errors and specify a value to be substituted in the cell in case of an error.
Syntax
IFERROR(value,value_if_error)
Value is the argument (formula) that is checked for an error.
Value_if_error is the value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
Example:
I have:
- Numeric values in cells A1, A2 and B2.
- In C2 I have the following formula:
=B2/A2*$A$1
If cell A2 has a value of 0 (zero) the formula will return #DIV/0. To trap this error in Excel 2003 you will use a formula like this:
=IF(ISNUMBER(B2/A2*$A$1);B2/A2*$A$1;0)
The ISNUMBER function, part of the first argument to the IF function; will decide if the value returned from our formula is a number. In this case it will accept the result; otherwise it will substitute a 0. Notice that our formula is supplied twice: as part of the first argument to the IF function, and as a second argument.
The IFERROR function is much simpler.
=IFERROR(B2/A2*$A$1;0)
In this example if there is no error the result of the formula: B2/A2*$A$1 will be returned and accepted, otherwise a 0 will be substituted.
