Microsoft Office Tutorials and References
In Depth Information
Formula Problems and Solutions
The #NAME? error occurs under these conditions:
h The formula contains an undefined range or cell name.
h The formula contains text that Excel interprets as an undefined name. A misspelled
function name, for example, generates a #NAME? error.
h The formula uses a worksheet function that’s defined in an add-in, and the add-in is not
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 references, but this does
not happen. In fact, Excel does not even provide a way to convert the names used in a
formula to the equivalent cell references!
The #NULL! error occurs when a formula attempts to use the 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 have no cells in common:
The following formula does not return #NULL! but instead displays the contents of cell B9 —
which represents the intersection of the two ranges:
A formula returns a #NUM! error if any of the following occurs:
h You pass a nonnumeric argument to a function when a numeric argument is expected.
h You pass an invalid argument to a function. For example, this formula returns #NUM!: