Summary: Determine if formula result errors exist in your Microsoft Excel 2003 spreadsheets.
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.
Add: Del.icio.us |
Digg |
Furl |
My Yahoo!
Last Modified on: July 13, 2008, at 12:04 A.M. EDT
Return to the Microsoft Excel 2003 Functions and Formulas page.
Search the Site: