Different Error Types

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 but has a date number format.
#CALC!Using a formula or expression that is not supported.
 Using an array that is nested inside another array.
 Using an array that includes one or more range references.
 Using an array that does not contain any items.
#DIV/0!(xlErrDiv)
 Dividing a number by zero.
 Dividing a number by the contents of an empty cell.
#FIELD!Referring to an invalid linked data type.
 Using a field that is not valid for that particular linked data type.
 Trying to use a linked data type on a cell reference, but the cell does not contain a linked data type.
 Using a field on a linked data type that does not have any data for that particular field.
#N/A(xlErrNA)
 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.
#NAME?(xlErrName)
 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.
#NULL!(xlErrNull)
 Using an incorrect range separator.
 Finding the intersection of two cell ranges that do not intersect.
#NUM!(xlErrNum)
 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.
#REF!(xlErrRef)
 Referring to a worksheet that does not exist.
 Referring to cells which have since been deleted.
 Using a Dynamic Data Exchange Link that is not available.
#SPILL!A dynamic array formula that is unable to spill into one or more adjacent cells because they contain data.
#UNKNOWN!The formula is referencing a data type that is not supported in that version of Excel.
#VALUE!(xlErrValue)
 The formula contains a parameter of the wrong datatype, i.e. the formula is expecting an integer but you have passed in some text.

Generating Errors

It can sometimes be useful to generate different types of errors when testing your workbooks and formulas.
The following table shows you examples of formulas that will generate the necessary error values.



© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext