Microsoft Office Tutorials and References

In Depth Information

**Formula Problems and Solutions**

#NAME? errors

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

installed.

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!

#NULL! errors

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:

=SUM(B5:B14 A16:F16)

The following formula does not return #NULL! but instead displays the contents of cell B9 —

which represents 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:

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!:

=SQRT(–1)