This tip was printed from the Tips for Spreadsheets website at http://tipsforspreadsheets.com/.

Microsoft Excel 2003 Functions and Formulas - Check for Errors

Depending on your Microsoft Excel 2003 spreadsheet, it might prove beneficial to determine whether or not a particular cell's value (which may be based on the result of a formula) is showing an error. Depending on the result, you may wish to alert the user with certain text in a highly-read section of the spreadsheet, perhaps in one of the top row cells.

The following formulas will check for error conditions:

=iserr(CELL)

Is cell CELL showing an error? This checks for any error except the #N/A (value not available) error.

=iserror(CELL)

Checks to see if cell CELL is showing an error. This will also detect the #N/A value.

=isna(CELL)

Check to see if cell CELL is showing the #N/A (value not available) error only.

These formulas will either return TRUE or FALSE:

Example spreadsheet cells:

A1: =5/0
A2: #N/A
A4: =ISERR(A1)
A5: =ISERROR(A1)
A6: =ISNA(A1)
A7: =ISERR(A2)
A8: =ISERROR(A2)
A9: =ISNA(A2)

This will return the values:

A4: TRUE
A5: TRUE
A6: FALSE
A7: FALSE
A8: TRUE
A9: TRUE

You can combine these functions with the =IF worksheet function to display values or perform other functions depending on the result.

For example:

A10: =IF(ISERROR(A1),"The value of A1 has an error. Please check.","")

This will return the value:

A10: The value of A1 has an error. Please check.



Press the "print" button on your browser or select "File" - "Print" to print this tip. Then, return to Microsoft Excel 2003 Functions and Formulas - Check for Errors.
 
Standard disclaimer applies - Read http://tipsforspreadsheets.com/copyright-disclaim.html.