Chapter 11: Circular References

Overview

• I often get a circular reference message from Excel. Does this mean I've made an error?
• How can I resolve circular references?

When you receive a message from Excel that your workbook contains a circular reference, it means there is a 'loop,' or dependency, between two or more cells in a worksheet. For example, a circular reference occurs if the value in cell A1 influences the value in C2, the value in cell C2 influences the value in cell D2, and the value in cell D2 influences the value in cell A1. Figure 11-1 illustrates the pattern of a circular reference.

 

Figure 11-1: Example of a loop causing a circular reference. 

As you'll soon see, you can resolve circular references by changing a settingin the Options dialog box. Click Tools, Options, select the Calculation tab, and then check the Iteration option.

I often get a circular reference message from Excel. Does this mean I've made an error?

A circular reference usually arises from a logically correct spreadsheet in which several cells exhibit a 'looping' relationship similar to Figure 11-1. Let's look at a simple example of a problem that cannot easily be solved in Excel without creating a circular reference. A small company earns $1,500 in revenues and incurs $1,000 in costs. They want to give 10 percent of their after-tax profits to charity. The tax rate is 40 percent. How much money should they give to charity? The solution to this problem is in the file Circular.xls, shown in Figure 11-2. 

Figure 11-2: A circular reference can occur when you're calculating taxes. 

We begin by naming the cells in D3:D8 with the corresponding names in cells C3:C8. Next we enter the firm's revenue, tax rate, and costs in D3:D5. To compute a contribution to charity as 10 percent of after-tax profit, we enter in cell D6 the formula 0.1*after_tax_profit. Then we determine before-tax profit in cell D7 by subtracting costs and the charitable contribution from revenues. Our formula in cell D7 is Revenues-Costs-Charity. Finally, we compute after-tax profit in cell D8 as:

(1-tax_rate)*before_tax_profit 

Excel indicates a circular reference in cell D6 (see the bottom of Figure 11-2). What's going on?

• Charity (cell D6) influences before-tax profit (cell D7).
• Before-tax profit (cell D7) influences after-tax profit (cell D8).
• After-tax profit (cell D8) influences before-tax profit.

Thus we have a loop of the form D6-D7-D8-D6, which causes the circular reference message. Our spreadsheet is logically correct; we have done nothing wrong! Still, we see from Figure 11-2 that Excel is giving us a nonsensical answer for charitable contributions.

How can I resolve circular references?

Resolving a circular reference is easy. Simply click Options on the Tools menu, click the Calculation tab, and check the Iteration box, as shown in Figure 11-3. 

Figure 11-3: Use the Iteration option in the Options dialog box to resolve a circular reference. 

When you check the Iteration option, Excel recognizes that your circular-reference has generated the following system of three equations with three unknowns: 

Charity = 0.1*(AfterTax profit)
BeforeTax Profit = Revenue - Charity - Costs
AfterTax Profit = (1 - Tax rate)*(BeforeTax Profit).

The three unknowns are Charity, BeforeTax Profit, and AfterTax Profit. When you check the Iteration box, Excel iterates (based on our limitation, 100 iterations will be used) to try and find a solution to all equations generated by the circular reference. From one iteration to the next, the values of the unknowns are changed by a complex mathematical procedure (Gauss-Seidel Iteration). Based on the value in the Maximum Change option (0.001 by default), Excel stops if the maximum change in any spreadsheet cell from one iteration to the next is at most 0.001. You should probably reduce the Maximum Change setting to a smaller number, such as 0.000001. Some complex spreadsheets might require more than 100 iterations before 'converging' to a resolution of the circularity. For our example, however, the circularity is almost instantly resolved, and we see the solution given in Figure 11-4. 

Figure 11-4: Excel runs the calculations to resolve the circular reference. 

Note that our charitable contribution of $28.30 is now exactly 10 percent of our after-tax profit of $283.01. All other cells in the spreadsheet are now correctly computed.

Here's one more example of a circular reference. In any Excel formula, you can refer to the entire column with a range name. For example, the formula AVERAGE(B:B) will average all cells in column B. This shortcut is useful if you're continually dumping new data (such as monthly sales) into column B. Then our formula always computes average sales, and we do not need to ever change it. The problem is, of course, that if we enter this formula in column B, we'll create a circular reference. If you select Tools, Options, Calculation and then check the Iteration box, circular references such as these will be resolved quickly.