Page 239 - Excel 2007 Bible

16_044039 ch11.qxp 11/21/06 11:04 AM Page 196
Part II
TABLE 11.3
Excel Error Values
Error Value
Explanation
The formula is trying to divide by zero. This also occurs when the formula attempts to divide by
#DIV/0!
what’s in a cell that is empty (that is, by nothing).
The formula uses a name that Excel doesn’t recognize. This can happen if you delete a name
#NAME?
that’s used in the formula or if you have unmatched quotes when using text.
The formula is referring (directly or indirectly) to a cell that uses the NA function to signal that
#N/A
data is not available. Some functions (for example, VLOOKUP) can also return #N/A.
The formula uses an intersection of two ranges that don’t intersect. (This concept is described
#NULL!
later in the chapter.)
A problem with a value exists; for example, you specified a negative number where a positive
#NUM!
number is expected.
The formula refers to a cell that isn’t valid. This can happen if the cell has been deleted from the
#REF!
worksheet.
The formula includes an argument or operand of the wrong type. An operand is a value or cell
#VALUE! Working with Formulas and Functions
reference that a formula uses to calculate a result.
Handling circular references
When you’re entering formulas, you may occasionally see a Circular Reference Warning message, shown in
Figure 11.14, indicating that the formula you just entered will result in a circular reference. A circular refer-
ence occurs when a formula refers to its own value — either directly or indirectly. For example, you create
a circular reference if you enter =A1+A2+A3 into cell A3 because the formula in cell A3 refers to cell A3.
Every time the formula in A3 is calculated, it must be calculated again because A3 has changed. The calcu-
lation could go on forever.
FIGURE 11.14
If you see this warning, you know that the formula you entered will result in a circular reference.

196