Microsoft Office Tutorials and References

In Depth Information

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 in-

tersect. 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)

You also see a #NULL! error if you accidentally omit an operator in a formula. For example, this formula is

missing the second operator:

= A1+A2 A3

#NUM! errors

A formula returns a #NUM! error if any of the following occurs:

• You pass a nonnumeric argument to a function when a numeric argument is expected: for example, $1,000

instead of 1000.

• You pass an invalid argument to a function. For example, this formula returns #NUM!:

=SQRT(–1)+

• A function that uses iteration can't calculate a result. Examples of functions that use iteration are IRR and

RATE.

• A formula returns a value that is too large or too small. Excel supports values between –1E–307 and

1E+307.

#REF! errors

The #REF! error occurs when a formula uses an invalid cell reference. This error can occur in the following

situations:

• You delete the row or column of a cell that is referenced by the formula. For example, the following formula

displays a #REF! error if row 1, column A, or column B is deleted:

=A1/B1