Microsoft Office Tutorials and References

In Depth Information

**Dealing with Circular References**

Table 2-4:
Excel Error Values
(continued)

Error Value

Explanation

#NULL!

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

intersection in Chapter 3.)

#NUM!

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

positive number is expected.

#REF!

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

worksheet.

#VALUE!

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.

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.

Depending on your settings, formulas that return an error may display a Smart Tag. You can click

this Smart Tag to get more information about the error or to trace the calculation steps that led

to the error. Refer to Chapter 21 for more information about this feature.

Dealing with Circular References

When you enter formulas, you may occasionally see a message from Excel like the one shown in

Figure 2-7. 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, =
into cell B3, and =
into cell A1, this produces a
B3

circular reference because the formulas create a circle where each formula depends on the one

before it. Every time the formula in A3 is calculated, it affects the formula in B3, which in turn

affects the formula in A1. The result of the formula in A1 then causes A3 to recalculate, and the

calculation circle starts all over again.

Figure 2-7:
Excel’s way of telling you that your formula contains a circular reference.