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




Custom Search