Intentional Circular References

As mentioned previously, you can use a circular reference to your advantage in some situations. A circular reference, if set up properly, can serve as the functional equivalent of a Do-Loop construct used in a programming language, such as VBA. An intentional circular reference introduces recursion or iteration into a problem. Each intermediate "answer" from a circular reference calculation functions in the subsequent calculation. Eventually, the solution converges to the final value.

By default, Excel does not permit iterative calculations. You must explicitly tell Excel that you want it to perform iterative calculations in your workbook. You do this by checking Enable Iterative Calculation in the Formulas section of the Excel Options dialog box (see Figure 16-3).

Figure 16-3: To calculate a circular reference, you must check the Enable Iterative Calculation check box.

Figure 16-4 shows a simple example of a worksheet that uses an intentional circular reference. A company has a policy of contributing five percent of its net profit to charity. The contribution itself, however, is considered an expense and is therefore subtracted from the net profit figure-producing a circular reference.

Figure 16-4: The company also deducts the 5 percent contribution of net profits as an expense (shown in cell B3), creating an intentional circular reference.

Note 

You cannot resolve the circular reference unless you turn on the Enable Iterative Calculation setting.

The text in column A corresponds to the named cells in column B, and cell C3 is named Pct. The Contributions cell (B3) contains the following formula:

 =Pct*Net_Profit 

The Net_Profit cell (B4) contains the following formula:

 =Gross_Income-Expenses-Contributions 

These formulas produce a resolvable circular reference. When you change either the Gross_Income or the Expenses cell, Excel keeps calculating until the formula results converge on a solution.

Note 

A reader of the first edition of this book 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 

On the CD 

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).xlsm.

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

• 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.

• Maximum iterations: Determines the maximum number of iterations that Excel will perform. This value cannot exceed 32,767 and cannot be less than 1.

• Maximum change: Determines when iteration stops. For example, if this setting is .01, iterations stops when a calculation produces a result that differs by less than 1 percent of the previous value.

Note 

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.

To get a feel for how this works, open the example workbook presented in the previous section (refer to Figure 16-4). Then perform the following steps:

1. Ensure the Enable Iterative Calculation check box is checked as described above.

2. Set the Maximum iterations setting to 1.

3. Set the Maximum change setting to .001.

4. Enter a different value into the Gross_Income cell (cell B1).

5. Press F9 to calculate the sheet.

Because the Maximum iteration setting is 1, pressing F9 performs just one iteration. You'll find that the Contributions cell has not converged. Press F9 a few more times, and you'll see the result converge on the solution. When the solution is found, pressing F9 has no noticeable effect. If the Maximum iterations setting reflects a large value, the solution appears almost immediately (unless it involves some slow calculations).