Tips & Tricks for Excel, Word, PowerPoint and Other Applications
Excel Error Messages
Why It Matters To You
Excel displays one of several error values when a formula cannot properly evaluate a result. Each error message has a different meaning and understanding what each means helps you get to the root problem.How to ...
| Error Message | Meaning |
|---|---|
| #VALUE | Wrong type of argument or operation is used. the formula may require a numerical value but refer to text, or the dimensions of a matrix are wrong. |
| #DIV/0 | A formula contains a division by zero argument. The formula may refer to a blank cell in the denominator. |
| #NAME? | Excel does not recognize text in a formulA. The formula may contain a misspelled range name or function, or contain text that should be in quotes. |
| #NA | A necessary value is not available for a formula or function. The function cannot find a value in a lookup function or may be missing arguments. |
| #REF! | An invalid cell reference. Cells referenced in a formula may have been moved, deleted, or copied with an invalid reference. |
| #NUM | A problem with a number in a formula or function. An argument requires a number (not text) or the result is too big or small for Excel. |
Using Error Values
Once you understand why error messages occur, you can use this information in your analysis. For example, when a VLOOKUP/HLOOKUP function cannot find a match, it will return a #N/A result.
| Function | Description |
|---|---|
| ISERR | Returns TRUE if Value is any error value except #N/A (Value Not Available). |
| ISERROR | Returns TRUE if Value is any error value: #VALUE!, #DIV/0, #NAME?, #N/A, #REF!, or #NUM. |
| ISNA | Returns TRUE if Value is the #N/A (Value Not Available) error message. |
We can use this information in one of several ways. See Introduction to Logical Statements and Logical Math for a review.
- Use the raw TRUE or FALSE value. Remember that TRUE=1 and FALSE=0 and we can use these values in mathematical operations. For example, when matching against multiple lists, you could sum up the TRUE values (e.g., no match) to count how many ‘no matches’ were found.
- Use the returned value in a logical statement to condition the value. For example, if your VLOOKUP finds no match, then you could replace the error message with 1) a blank cell or 2) the text “no match found”. This is useful when the error messages get in the way of calculations.
Be careful with ISERROR though, since it can hide valuable error messages that can help you debug your work.
Notes
| Last updated | 9/2/07 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips | None |