Chapter 32: Making Your Worksheets Error-Free
655
The #N/A error also occurs when a LOOKUP function (HLOOKUP, LOOKUP, MATCH, or VLOOKUP)
can’t find a match.#NAME? errors
The #NAME? error occurs under these conditions:l The formula contains an undefined range or cell name.l (^) The formula contains text that Excel interprets as an undefined name. A misspelled func-
tion name, for example, generates a #NAME? error.
l (^) The formula uses a worksheet function that’s defined in an add-in, and the add-in is not
installed.
Caution
Excel has a bit of a problem with range names. If you delete a name for a cell or range and the name is used in
a formula, the formula continues to use the name, even though it’s no longer defined. As a result, the formula
displays #NAME?. You may expect Excel to automatically convert the names to their corresponding cell refer-
ences, but this doesn’t happen. n
#NULL! errors
A #NULL! error occurs when a formula attempts to use an intersection of two ranges that don’t
actually intersect. Excel’s intersection operator is a space. The following formula, for example,
returns #NULL! because the two ranges don’t intersect:
=SUM(B5:B14 A16:F16)
The following formula doesn’t return #NULL! but displays the contents of cell B9, which repre-
sents the intersection of the two ranges:
=SUM(B5:B14 A9:F9)
#NUM! errors
A formula returns a #NUM! error if any of the following occurs:
l (^) You pass a non-numeric argument to a function when a numeric argument is expected.
l You pass an invalid argument to a function. For example, this formula returns #NUM!:
=SQRT(-12).
l A function that uses iteration can’t calculate a result. Examples of functions that use itera-
tion are IRR and RATE.
l A formula returns a value that is too large or too small. Excel supports values between
–1E-307 and 1E+307.