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 specifi 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
Search JabSto ::




Custom Search