Circular references in Excel usually mean an error. Two common examples:
A cell is included in its own formula. For example, the formula "B6 = SUM(B2.B6)". The fix is to change the formula to "B6 = SUM(B2.B5)"
Two cells refer to each other. Cell B2 has a formula "=C2" and C2 has a formula "=B2". One formula needs to change.
With some programs, this would create an endless loop. Fortunately, Excel picks up on the error and rather than crashing, it stops trying to calculate and returns an error message called the Circular References.
However, in the real world, we can't always make it that simple. Sometimes there is an interdependency. Here is an example below. To do the exercise, open the Circular Reference Iteration file. The fix is in the Circular Reference Iteration Result file.
We want to calculate the year end bonus, which is based on pretax income, as shown in the formula below. However, the bonus is also an expense that is deducted to calculate pre-tax income.
Try doing the calculation. Click the F9 key since we are set on manual recalculation. You will get a Circular Reference error.
Click Cancel. You will see the Circular Reference error at the bottom referencing cell B8.
Are we out of luck? Do we have to go back to HR and legal and tell them we need to change how bonuses are calculated? Fortunately not. Excel is flexible enough to let you work your way out of this one. Here's how.
In the menu bar, select Tools, then Options, then go to the Calculations tab.
In the middle, notice the Iteration section. Click on this. We will leave the iterations at 100. Since we are calculating to the nearest penny, we want the maximum change to be 0.01.
Then click OK, then F9 to calculate. Now the formula can calculate.
Try different bonus percentages. You can calculate now without getting the Circular Reference error.
While we set the calculations to manual for displaying this exercise, you can change back to Automatic calculation by going back to the above Options dialog box from the Tools menu.
Most times when there is a circular reference, you have an error that needs to be fixed. However, when there is a true interdependency, you can use iterations to bypass the circular reference error.
However, here are some things you should now about iterations:
The settings will apply to all worksheets in a workbook. You could miss a circular reference in another worksheet. Therefore we recommend putting in a codependent formula like the bonus calculation at the very end of your model.
The settings will also apply to all open workbooks. If you have this workbook opened first and then open another workbook, the iteration settings apply to that workbook also. You have the same risk of missing a circular reference in that workbook.
If you open another workbook first that has iterations turned off and then open this workbook, then you will get a circular reference error. You will have to go in and turn back on iterations as we did in this exercise.