Microsoft Office Tutorials and References

In Depth Information

**Intentional Circular References**

A reader of the first edition of this topic pointed out another way to approach this

problem without using a circular reference. Use the following formula to calculate the

Net_Profit
cell:

=(Gross_Income-Expenses)/(1+Pct)

Then calculate the
Contributions
cell using this formula:

=Pct*Net_Profit

You can access the workbook,
net profit (circular).xlsm
, shown in Figure 16-4,

on the companion CD-ROM. For your convenience, the worksheet includes a button

that, when clicked, executes a macro that displays a dialog box that lets you toggle the

iteration setting. This makes it easy to experiment with various iteration settings.

Depending on your security settings, you may see a Security Warning when you open

this workbook. In addition, the CD-ROM contains a file that demonstrates how to

perform this calculation without using a circular reference, named
net profit (not

circular).xlsx
.

The Formula tab of the Excel Options dialog box includes three controls relevant to circular

references:

h
Enable Iterative Calculation check box:
If unchecked, Excel does not perform iterative

calculations, and Excel displays a warning dialog box if you create a formula that has a

circular reference. By default, this box is unchecked. When creating an intentional circular

reference, you must check this check box.

h
Maximum Iterations:
Determines the maximum number of iterations that Excel will

perform. This value cannot exceed 32,767 and cannot be less than 1.

h
Maximum Change:
Determines when iteration stops. For example, if this setting is .01,

iteration stops when a calculation produces a result that differs by less than 1 percent of

the previous value.

Calculation continues until Excel reaches the number of iterations specified in the

Maximum Iterations box, or until a recalculation changes all cells by less than the

amount you set in the Maximum Change box (whichever is reached first). Depending on

your application, you may need to adjust the settings in the Maximum Iterations field or

the Maximum Change field. For a more accurate solution, make the Maximum Change

field smaller. If the result doesnâ€™t converge after 100 iterations, you can increase the

Maximum Iterations field.