Datasheet

133
CHAPTER 4
■
WORKING DATA MAGIC WITH CALCULATIONS
Table 4.2. Error values
This error Usually means this To fix it, do this
##### The column isn’t wide enough Widen the column.
to display the value.
#VALUE! Wrong type of argument, Check values, references, and
value, or cell reference (for arguments; make sure references
example, calculating a cell are valid.
with the error value #N/A).
#DIV/0! Formula is attempting to Change the value or cell reference
divide by zero or by an so the formula doesn’t divide by
empty cell. zero.
#NAME? Formula is referencing an Make sure the name still exists or
invalid or nonexistent name. correct the misspelling.
#N/A Usually means no value is In a lookup formula, make sure the
available or inappropriate lookup table is sorted correctly.
arguments were used.
#REF! Excel can’t locate the Click Undo immediately to restore
referenced cells (for example, references, and then change
if referenced cells are deleted). formula references or convert
formulas to values.
#NUM! Incorrect use of a number Make sure that the arguments are
(such as SQRT(-1), which is correct, and that the result is
not possible), or formula between –1*10
307
and 1*10
307
.
result is a number too large
or too small to be displayed.
#NULL! Reference to intersection Check for typing and reference
of two areas that do not errors.
intersect.
Circular The formula refers to itself, Click OK in the message; look at the
reference either directly or indirectly. Status bar to see which cell contains
message the circular reference, and remove
references to the formula cell.
Locating errors in formulas
On the Formula Auditing toolbar (shown in Figure 4.28), click the Error
Checking button. The tool checks all cells in the worksheet for any sign
of an error (and picks up things that aren’t errors, such as numbers
deliberately preceded by an apostrophe to make them text).
09_763217 ch04.qxp 1/18/06 11:23 PM Page 133