Microsoft Office Tutorials and References
In Depth Information
Formula Errors
Formula Errors
#NUM!
There’s a problem with one of the numbers you’re using. For example, this
error code appears when a calculation produces a number that’s too large or too
small for Excel to deal with.
#DIV/0
You tried to divide by zero. This error code also appears if you try to divide by
a cell that’s blank, because Excel treats a blank cell as though it contains the
number 0 for the purpose of simple calculations with the arithmetic operators.
(Some functions, like AVERAGE(), are a little more intelligent and ignore blank
cells.)
#REF!
Your cell reference is invalid. This error most often happens if you delete or
paste over the cells you were using, or if you try to copy a cell from one
worksheet to another.
#N/A
The value isn’t available. This error can happen if you try to perform certain
types of lookup or statistical functions that work with cell ranges. For example,
if you use a function to search a range and it can’t find what you need, you
may get this result. Sometimes people enter a #N/A value manually in order to
tell Excel to ignore a particular cell when creating charts and graphs. The easiest
way to do this is to use the NA() function (rather than entering the text # N/A ).
#NULL!
You used the intersection operator incorrectly. Remember, the intersection
operator finds cells that two ranges share in common. This error results if there
are no cells in common. Oftentimes, people use the intersection operator by
accident, as the operator’s just a single space character.
########
This code isn’t actually an error condition—in all likelihood, Excel has
successfully calculated your formula. However, the formula can’t be displayed in the
cell using the current number format. To solve this problem, you can widen
the column, or possibly change the number format (page 441) if you require a
certain number of fixed decimal places.
troUBleshootinG moment
Circular References
One of the more aggravating problems that can happen
with formulas is the infamous circular reference . A circular
reference happens when you create a formula that
depends, indirectly or directly, on its own value. For example,
consider what happens if you enter the following formula
in cell B1.
=B1+10
In order for this formula to work, Excel would need to take
the current B1 value, and add 10. However, this operation
changes the value of B1, which means Excel needs to
recalculate the formula. If unchecked, this process would
continue in an endless loop without ever producing a value.
to a cell in another cell that refers back to the original cell.
This is what’s known as an indirect circular reference , but
the problem is the same.
Ordinarily, Excel doesn’t allow circular references. When
you enter a formula that contains a circular reference,
Excel displays an error message and forces you to edit the
formula until you’ve removed the circular reference.
However, you can configure Excel to allow circular references
by modifying the calculation settings, as described in the
Formulas section of the Excel Options dialog box. In this
case, Excel repeats the loop a fixed number of times, or
until the value seems to settle down and stop changing.
More subtle forms of circular references are possible. For
example, you can create a formula in one cell that refers
Search JabSto ::




Custom Search