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