Microsoft Office Tutorials and References
In Depth Information
Working with circular references
If a circular-reference warning surprises you, this usually means you made an error in a
formula. If the error isn’t obvious, verify the cells that the formula refers to by using the
built-in, formula-auditing features. For details, see “Auditing and documenting worksheets”
in Chapter 8.
When a circular reference is present in the current worksheet, the status bar displays the
text Circular References followed by the cell address, indicating the location of the circular
reference on the current worksheet. If Circular References appears without a cell address,
the circular reference is located on another worksheet.
As you can see in Figure 12-30, when you click the arrow next to the Error Checking button
on the Formulas tab and click Circular References, any circular references that exist on the
current worksheet are listed on a menu (which appears only if a circular reference is
present). Click the reference listed on this menu to activate the offending cell.
Figure 12-30 The Circular References menu appears if any circular references are present.
You can resolve many circular references. Some circular formulas are useful or even
essential, such as the set of circular references shown in Figure 12-31. These formulas are circular
because the formula in cell N30 depends on the value in N31, and the formula in N31
depends on the value in N30.
Figure 12-31 illustrates a circular reference scenario called convergence : the difference
between results decreases with each iterative calculation. In the opposite process, called
divergence , the difference between results increases with each calculation.