Chapter 10: Introducing Formulas and Functions
219
In some cases, Excel won’t even let you enter an erroneous formula. For example, the following
formula is missing the closing parenthesis:=A1*(B1+C2If you attempt to enter this formula, Excel informs you that you have unmatched parentheses, and
it proposes a correction. Often, the proposed correction is accurate, but you can’t count on it.Table 10.3 lists the types of error values that may appear in a cell that has a formula. Formulas may
return an error value if a cell to which they refer has an error value. This is known as the ripple
effect — a single error value can make its way into lots of other cells that contain formulas that
depend on that one cell.TABLE 10.3
Excel Error Values
Error Value Explanation#DIV/0! The formula is trying to divide by zero. This also occurs when the formula attempts to
divide by what’s in a cell that is empty (that is, by nothing).
#NAME? The formula uses a name that Excel doesn’t recognize. This can happen if you delete a
name that’s used in the formula or if you have unmatched quotes when using text.
#N/A The formula is referring (directly or indirectly) to a cell that uses the NA function to sig-
nal that data is not available. Some functions (for example, VLOOKUP) can also return
#N/A.
#NULL! The formula uses an intersection of two ranges that don’t intersect. (This concept is
described later in the chapter.)
#NUM! A problem with a value exists; for example, you specified a negative number where a
positive number is expected.
#REF! The formula refers to a cell that isn’t valid. This can happen if the cell has been deleted
from the worksheet.
#VALUE! The formula includes an argument or operand of the wrong type. An operand is a value
or cell 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 10.14, indicating that the formula you just entered will result in a circular refer-
ence. A circular reference occurs when a formula refers to its own value — either directly or indi-
rectly. 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 cal-
culated again because A3 has changed. The calculation could go on forever.