Chapter 10: 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 Microsoft Office Excel displays a message 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 10-1 illustrates the pattern of a circular reference.

Figure 10-1: A loop causing a circular reference

As you’ll soon see, you can resolve circular references by clicking the Microsoft Office Button followed by Excel Options. Then choose Formulas and check the Enable Iterative Calculation box.

• 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 consistent worksheet in which several cells exhibit a “looping” relationship similar to that illustrated in Figure 10-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. Their tax rate is 40 percent. How much money should they give to charity? The solution to this problem is in the file Circular.xlsx, shown in Figure 10-2.

  
  Figure 10-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 D8 (see the bottom left-hand corner of file Circular.xlxs). What’s going on?

  1. Charity (cell D6) influences before-tax profit (cell D7).

  2. Before-tax profit (cell D7) influences after-tax profit (cell D8).

  3. After-tax profit (cell D8) influences charity.

• Thus we have a loop of the form D6-D7-D8-D6 (indicated by the blue arrows in Figure 10-2), which causes the circular reference message. Our worksheet is logically correct; we have done nothing wrong! Still, we see from Figure 10-2 that Excel is giving us an incorrect answer for charitable contributions.

• How can I resolve circular references?

• Resolving a circular reference is easy. Simply click the Microsoft Office Button in the upper-left corner of the Ribbon, and then click Excel Options. Choose Formulas in the left pane, and check the Enable Iterative Calculation box in the Calculation Options section, as shown in Figure 10-3.

  
  Figure 10-3: Use the Enable Iterative Calculation option to resolve a circular reference.

• When you activate the Enable Iterative Calculation 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 activate the Enable Iterative Calculation option, Excel iterates (based on our limitation, 100 iterations will be used) to seek 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). Excel stops if the maximum change in any worksheet cell from one iteration to the next is smaller than the Maximum Change value (0.001 by default). You can reduce the Maximum Change setting to a smaller number, such as 0.000001. If you do not reduce the Maximum Change to a smaller number, you might find Excel assigning a value of, for example, 5.001 to a cell that should equal 5, and this is annoying! Also, some complex worksheets 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 10-4.

  
  Figure 10-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 worksheet are now correctly computed.

• Here’s one more example of a circular reference. In any Excel formula, you can refer to an entire column or row by name. For example, the formula AVERAGE(B:B) will average all cells in column B. The formula =AVERAGE(1:1) will average all cells in row 1. This shortcut is useful if you’re continually dumping new data (such as monthly sales) into a column or row. 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 the column or row that it refers to, we’ll create a circular reference. By activating the Enable Iterative Calculation option, circular references such as these will be resolved quickly.