Microsoft Office Tutorials and References

In Depth Information

**Handling circular references**

If the entire cell is i filled with hash-mark characters, the column isn’t wide enough to display the value. You can either

widen the column or change the number format of the cell.

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+C2

If 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 15.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 15.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 signal 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.

#NUM!

A problem with a value exists; for example, you speciﬁ ed 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.)

15

Handling circular references

When you’re entering formulas, you may occasionally see a warning message like the one

shown in Figure 15.14, indicating that the formula you just entered will result in a
circular