Microsoft Office Tutorials and References

In Depth Information

**Potential Problems with Intentional Circular References**

When you press F9 to calculate the worksheet, the value in A2 increments, thereby changing the

position of the round marker on the chart. Press F9 repeatedly and watch the marker move along

the line. The amount of marker movement depends on two factors:

h
The increment value in the formula (set at .005)

h
The Maximum Iterations setting in the Formula tab of the Excel Options dialog box

When Maximum Iterations is 100 and the increment is .005, each calculation increases the value

in cell A2 by 0.5. The IF function in the formula resets the value to 0 when it exceeds 12.6.

Therefore, the marker returns to the left side of the chart and starts over.

This example, named
iterative chart animation.xlsx
, is available on the

companion CD-ROM.

Potential Problems with Intentional

Circular References

Although intentional circular references can be useful, using this feature has some potential

problems. Perhaps the best advice is to use this feature with caution, and make sure you understand

how it works.

To take advantage of an intentional circular reference, you must have the Enable Iterative

Calculation setting in effect. When that setting is in effect, Excel does not warn you of circular

references. Therefore, you run the risk of creating an accidental circular reference without even

knowing about it.

The number of iterations specified in the Maximum iteration field applies to all formulas in the

workbook, not just those that use circular references. If your workbook contains many complex

formulas, these additional iterations can slow things down considerably. Therefore, when you use

intentional circular references, keep your worksheets very simple and close all workbooks that

you aren’t using.

You may need to distribute a workbook that uses intentional circular references to other users. If

Excel’s Iteration setting is not active when you open the workbook, Excel displays the circular

reference error message, which probably confuses all but the most sophisticated users.