Microsoft Office Tutorials and References

In Depth Information

**Circular Reference Examples**

h
If you have all workbooks closed and you create a new workbook, the new workbook

uses the same Calculation mode as the last closed workbook. The exception is if you

create the workbook from a template, the workbook uses the calculation mode specified in

the template.

h
If the mode of calculation in a workbook changes and you save the file, the current mode

of calculation saves with the workbook.

Bottom line? When you open a workbook that uses iteration, there is no guarantee that the

setting saved with your workbook will be the setting that is in effect when you open the workbook.

When the Enable Iterative Calculation setting is in effect, Excel will never display the

Circular References warning dialog box and will not display the Circular References

message in the status bar. Therefore, you may create an unintentional circular reference

and not even know about it.

Circular Reference Examples

Following are a few more examples of using intentional circular references. They demonstrate

creating circular references for entering unique random numbers, solving a recursive equation,

solving simultaneous equations, and animating a chart.

For these examples to work properly, the Enable Iterative Calculation setting must be

in effect. Choose Excel Options, navigate to the Formulas section, and mark the Enable

Iterative Calculation check box.

Generating unique random integers

This example demonstrates how to take advantage of a circular reference to generate unique

(nonduplicated) random integers in a range. The worksheet in Figure 16-5 generates 15 random

integers between the values specified in cells E1 and E2.

Column B contains formulas that count the number of times a particular number appears in the

range A1:A15 (named
RandomNumbers
). For example, the formula in cell B1 follows. This formula

displays the number of times the value in cell A1 appears in the
RandomNumbers
range:

=COUNTIF(RandomNumbers,A1)

Cell B17, named
Dupes,
displays the number of duplicated values using this formula:

=SUM(B1:B15)-COUNTA(B1:B15)