Part II: Working with Formulas and Functions
In some cases, Excel won’t even let you enter an erroneous formula. For example, the fol-
lowing formula is missing the closing parenthesis:
=A1*(B1+C2
If you attempt to enter this formula, Excel informs you that you have unmatched parenthe-
ses, and it proposes a correction. Often, the proposed correction is accurate, but you can’t
count on it.
Table 9.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 9.3 Excel Error Values
Error Value Explanation
#DIV/0! The formula is trying to divide by zero. Because Excel applies a value of 0 to
empty cells, this error also occurs when the formula attempts to divide by a cell
that is blank or has a value of 0.
#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, if you misspell a name and then hit
Enter, 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 signal that data is not available. For instance, the following formula returns
an #N/A error if the A1 is empty: =IF(A1=””, NA(), A1)
Some lookup functions (for example, VLOOKUP and MATCH) can also return #N/A
when they do not find a match.
#NULL! The formula uses an intersection of two ranges that don’t intersect. (This con-
cept is described later in the chapter.)
#NUM! A problem with a value exists; for example, you specified a negative number as
an argument where a positive number is required.
#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 warning message indicating
that the formula you just entered will result in a circular reference. A circular reference
occurs when a formula refers to its own cell—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
