Microsoft Office Tutorials and References

In Depth Information

**Working with circular references**

Figure 12-31
The discount formula in cell N30 is circular because it depends on the total, which

in turn depends on the discount value in N30.

When Excel detects a circular reference, tracer arrows appear on the worksheet. To draw

additional arrows to track down the source of an unintentional circular reference, select the

offending cell and then click Trace Precedents on the Formulas tab to draw tracer arrows to

the next level of precedent cells, as shown in Figure 12-31.

For more information about tracer arrows and other auditing features, see “Auditing and

documenting worksheets” in Chapter 8.

You’ll find the Circular Reference.xlsx file with the other examples on the companion

website.

After you dismiss the error message shown earlier in Figure 12-29, the formula will not

resolve until you allow Excel to recalculate in controlled steps. To do so, click the File tab,

Options, Formulas category, and in the Calculation Options section, select the Enable

Iterative Calculation check box. Excel recalculates all the cells in any open worksheets that

contain a circular reference.

If necessary, the recalculation repeats the number of times specified in the Maximum

Iterations box (100 is the default). Each time Excel recalculates the formulas, the results in

the cells get closer to the correct values. If necessary, Excel continues until the difference

between iterations is less than the number typed in the Maximum Change text box (0.001

is the default). Thus, using the default settings, Excel recalculates either a maximum of 100

times or until the values change less than 0.001 between iterations, whichever comes first.

If the word
Calculate
appears in the status bar after the iterations are finished, more

iterations are possible. You can accept the current result, increase the number of iterations, or

lower the Maximum Change threshold. Excel does not repeat the “Cannot Resolve Circular

Reference” error message if it fails to resolve the reference. You must determine when the