Handling Iterative Calculations With Ease
Though it is usually preferable to avoid circular references, they will appear occasionally when building complex calculations. One common example is in tax accrual calculations, where federal income tax is deductible on state tax returns. Here, since federal tax is deductible on the state return, and state tax is deductible on the federal return, a circular reference exists by the very nature of the calculation. By default, Excel treats such calculations as errors. In this tip, you will learn how to enable circular, or iterative, calculations.
An iteration is repeatedly recalculating a worksheet until a specific numeric condition is met. Excel cannot calculate a formula that refers to the cell – either directly or indirectly – that contains the formula. This is called a circular reference and circular references, by default, can iterate indefinitely, leading to errors in worksheets, as shown in Figure 1.
Figure 1 - Example of Erroneous Circular Reference in Excel
Many users take advantage of Excel's Formula Auditing functions to identify circular referencing formulas and change them so that the circular references disappear. However, it is not always necessary to eliminate these references if you understand why they have appeared, what impact they have, and how to ensure that the presence of these formulas is not affecting the accuracy of the worksheet. The most common way of accommodating formulas with circular references is to change the number of iterations calculated by Excel.
By accessing Excel's Options and choosing the Formulas tab, you can check the box labeled Enable iterative calculation as shown in Figure 2.
Figure 2 - Enabling Iterative Calculations in Excel
Enabling iterative calculations causes Excel to calculate worksheets containing circular referencing formulas up to the number of times specified in the Maximum iterations field, or until the value change in the Maximum change field is reached. Though 100 iterations is the default maximum number of iterations and 0.001 is the default maximum change value, usually Excel reaches sufficiently accurate results in as few as three iterations.
Returning to the worksheet presented in Figure 1, we find that after enabling iterative calculations in the workbook, the formulas function properly and calculate the correct amount of tax for both the federal and state income tax returns, as shown in Figure 3.
Figure 3 - Properly Functioning Circular Referencing Formulas
Many Excel users view circular referencing formulas as errors. However, while we should generally avoid circular references, they are not always errors and, in fact, sometimes are necessary to address complex situations. As you have seen in this tip, the key to working with circular references is to enable Excel's iterative calculation tool. Upon doing so, you should find that your circular referencing formulas function as you intend them to.
For a video demonstration of this tip, visit www.tinyurl.com/k2tips123.