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
Search JabSto ::




Custom Search