Microsoft Office Tutorials and References

In Depth Information

Error

Value

Explanation

The formula attempts to divide by zero (an operation not allowed on this planet).

This also occurs when the formula attempts to divide by an empty cell.

#DIV/0!

The formula uses a name that Excel doesn't recognize. This can happen if you

delete a name used in the formula or if you misspell a function.

#NAME?

The formula refers (directly or indirectly) to a cell that uses the NA function to sig-

nal unavailable data. This error also occurs if a lookup function does not find a

match.

#N/A

The formula uses an intersection of two ranges that don't intersect. (I describe

range intersection in Chapter 3.)

#NULL!

A problem occurs with a value; for example, you specify a negative number where

a positive number is expected.

#NUM!

The formula refers to an invalid cell. This happens if the cell has been deleted

from the worksheet.

#REF!

The formula includes an argument or operand of the wrong type. An
operand

refers to a value or cell reference that a formula uses to calculate a result.

#VALUE!

If the entire cell fills with hash marks (#########), this usually means that the column

isn't wide enough to display the value. You can either widen the column or change the

number format of the cell. The cell also fills with hash marks if it contains a formula that

returns an invalid date or time.

Refer to Chapter 22 for more information about identifying and tracing errors.

Dealing with Circular References

When you enter formulas, you may occasionally see a message from Excel like the one shown in Figure 2-8.

This indicates that the formula you just entered will result in a
circular reference.

A circular reference occurs when a formula refers to its own value, either directly or indirectly. For example, if

you type
=A1
into cell A3,
=A3
into cell B3, and
=B3
into cell A1, this produces a circular reference because