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
Potential Problems with Intentional
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.