Different Types of Errors
Also see the Functions > Troubleshooting page.
The following errors can be returned from your formulas.
Even formula that have the correct syntax can occasionally return error values.
The following is a list of the possible error values and the reasons the error might have been generated.
|######||This is displayed when a column is not wide enough to display the result. This is not technically an error.|
|Using a negative date or time.|
|A ##### error value occurs when the cell contains a number, date, or time that is wider than the cell or when the cell contains a date or time formula that produces a negative result. Try increasing the width of the column.|
|A number which is not a valid date serial number will have a date format ?|
|#DIV/0!||Dividing a number by zero.|
|Dividing a number by the contents of an empty cell. (xlErrDiv)|
|#N/A||Using VLOOKUP, HLOOKUP or MATCH functions when they do not return a match (or the list is not sorted).|
|Using a custom worksheet function that is not available.|
|Using a worksheet function without submitting all the required parameters.|
|You have used the NA() worksheet function. (xlErrNA)|
|#NAME?||Referring to a named range that does not exist.|
|Text in a formula is unrecognised|
|Using an Analysis-ToolPak function when the add-in is not loaded.|
|Using a worksheet function that does not exist (or has been spelt incorrectly).|
|Using labels when labels are not allowed.|
|Entering text that is not enclosed in double speech marks ("some text").|
|Using an invalid cell range reference.|
|Referring to another worksheet that does not exist. (xlErrName)|
|#NULL!||Using an incorrect range separator.|
|Finding the intersection of two cell ranges that do not intersect. (xlErrNull)|
|#NUM!||Passing the incorrect argument to a worksheet function.|
|Using a function that iterates (e.g. IRR or RATE) and no result can be found.|
|The number returned is too big or too small for Excel to recognise it. (xlErrNum)|
|#REF!||Referring to a worksheet that does not exist.|
|Referring to cells which are then deleted.|
|Using a Dynamic Data Exchange Link that is not available. (xlErrRef)|
|#VALUE!||The formula contains a parameter of the wrong datatype, i.e. the formula is expecting an integer but you have passed in some text. (xlErrValue)|
Finding any Formula Errors
Sometimes when you enter a formula an error will occur. This is to indicate that the formula syntax is incorrect.
If this error occurs press OK to be taken back to the formula bar. You can either correct the formula or press ESC to remove the formula completely.
This error may be caused by missing parentheses or incorrect arguments being passed to functions (e.g. passing a string when it is expecting a number).
To quickly locate any cells that contain errors, select (Home tab, Find & Select > GoTo Special) and tick the Formulas, Errors checkbox.
Trying to Preventing Errors
A common method used to try and eliminate errors from appears on your worksheet is to use the ISERROR() worksheet function as a wrapper.
The formula in cell B2 tries to divide D2 by D3, which generates an error as division by zero is not possible.
The formula in cell B3 includes the ISERROR() function as a wrapper around the formula.
Generating Errors for Testing
The following table shows you examples of formulas that will generate the necessary error values.
© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited