Tuesday, June 7, 2011

How to deal with Excel Formula Errors - using ISERROR & IFERROR

ISERROR



What Does It Do ?
This function tests a cell or calculation to determine whether an error has been generated. It will show TRUE for any type of error and FALSE if no error is found.

Syntax
=ISERROR(CellToTest) The CellToTest can be a cell reference or a formula.

Formatting
No special formatting is needed.

Example
The following tables was used to calculate the difference between two dates.

Table 1 shows an error due to the fact that the first entry was entered using an inappropriate date format.

Table 2 shows how the =ISERROR() function has been used to trap the error and inform the user that there has been an error in the data entry.


IFERROR

Syntax

IFERROR(value,value_if_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!.

Remarks

  • If value or value_if_error is an empty cell, IFERROR treats it as an empty string value ("").
  • If value is an array formula, IFERROR returns an array of results for each cell in the range specified in value. See the second example below.


Twitter Bird Gadget