Making Sense of Circular References in Excel
In this article, you will learn about Circular References in Excel.
What are circular references in Excel?
A circular reference in Excel occurs when a formula within a cell directly or indirectly refers back to its own cell for a value. To visualize this, imagine trying to solve a mathematical problem where the solution depends on knowing the solution itself. This creates a paradox of sorts – an endless loop of calculations that Excel, by its inherent design, cannot solve. And so with this infinite loop, circular references are typically considered errors. An example of a circular reference can be seen below.
In this case, a circular reference exists in cell B4 because the formula includes a calculator of itself.
Why are circular references problematic in Excel?
Circular references are generally seen as problematic due to the errors and computational issues they can introduce. The primary issue is the generation of incorrect results. As a circular reference creates an infinite loop of calculations, Excel cannot deliver a definitive and accurate result.
Another issue is the potential for causing Excel to slow down significantly or even crash. The constant calculations caused by a circular reference can consume significant processing resources, impacting the performance of your Excel program and potentially leading to application crashes.
Note: In some specific situations, you may intentionally use circular references for iterative calculations, such as those involving complex statistical models or financial modeling. But even then, you need to adjust Excel's settings to limit the number of iterations and prevent an endless loop. An example and further explanation of this can be seen later below.
Identifying Circular References
Excel is well-equipped to help users identify circular references. Whenever you enter a formula that results in a circular reference, Excel will display a warning to alert you. Simultaneously, the status bar at the bottom of the Excel window will indicate "Circular References" and will provide the address of the cell causing the issue.
Excel also includes a built-in feature that allows you to track down potential circular references. Located within the "Formulas" tab, the "Error Checking" tool will identify cells that may be causing calculation errors, including circular references. This process can be seen in the image below.
Resolving and Preventing Circular References
A circular reference often signals a logical error in the design of your worksheet. It indicates that a formula is referring back to itself in a way that isn't solvable, which likely means there's a mistake in how you've set up your calculations. This is something you would want to prevent or resolve.
The main approach to resolving circular references is to modify the problematic formula so it no longer refers back to its own cell. This generally involves rethinking the logic of your formula or employing different Excel functions to get your desired result.
When redesigning your worksheet or formula, keep the following tips in mind:
- Plan your formulas in advance to ensure they don't inadvertently create a circular reference.
- Use Excel's precedent and dependent tracing features to see which cells a formula refers to, and which formulas refer to a given cell. To do this select the cell in question and navigate to the Formula tab and then click Trace Precedents or Trace Dependents. Precedent cells are cells that are referred to by another cell (An arrow will be drawn from the precedented cells to the selected cell). Dependent cells are cells that refer to other cells (An arrow will be drawn from the selected cell to the dependent cells).
- Consider using absolute references when copying and pasting formulas to prevent unintended changes to cell references. In Excel, an absolute reference is a type of cell reference that doesn't change when it's copied or filled into other cells. It's marked by a dollar sign ($) before the column letter, row number, or both. (Ex: $A$1 or $A1 or A$1)
- Regularly use Excel's error-checking feature to catch circular references early.
How to allow circular references for iterative calculation
As mentioned above, while circular references are considered errors, in some cases some may use circular references as iterative calculations which can be helpful when wanting to repeat function calculations until a condition is met. With that said, to allow these calculations follow these steps:
For PC:
- Go to File and then click Options followed by Formulas.
- Under Calculation options check the box for Enable iterative calculation.
- Set the maximum iterations. This is the number of times the worksheet will recalculate. The default is 100.
- Set the maximum change. This is the smallest value that allows iteration to continue. The default is 0.001.
For Mac:
- Navigate to the Menu Bar of your Mac and click on the Excel menu and then click Preferences followed by Calculation in the Formulas and Lists section.
- In the calculation options, you then click the Use iterative calculation box.
- Set the maximum iterations. This is the number of times the worksheet will recalculate. The default is 100.
- Set the maximum change. This is the smallest value that allows iteration to continue. The default is 0.001.
The possible outcomes of using circular references for iterative calculations
When you enable iterative calculations in Excel to solve a circular reference, one of three main outcomes may occur:
- Convergence: This is the ideal scenario. Excel repeatedly recalculates the workbook and, after a certain number of iterations, the values settle on a stable solution. That is, the change in value from one iteration to the next becomes less than the "Maximum Change" value you specified in the settings or has reached the “Maximum Iterations” specified. The circular reference is effectively resolved.
- Divergence: In some cases, the iterative calculations do not converge on a stable solution. Instead, the values keep changing and do not settle, or they might even grow larger with each iteration. This could happen if your formulas amplify changes rather than reducing them, or if there's a logical issue with your formulas. If Excel hits the maximum number of iterations you've specified without finding a solution, it will stop and leave the circular reference unresolved.
- Oscillation: In this scenario, the values do not converge to a single solution, but rather oscillate between two or more values. This might happen if, for example, you have a formula that switches between positive and negative values on each iteration. As with divergence, if Excel hits the maximum number of iterations you've specified without finding a stable solution, it will stop.
Keep in mind that iterative calculations can potentially slow down Excel, particularly if your workbook is complex or large, or if you've set a high maximum number of iterations. Always use caution when dealing with circular references and iterative calculations, and aim to design your formulas to avoid circular references where possible.
An example of using circular references for iterative calculations in Excel
Suppose your business makes a profit of $500,000 and you want to calculate the profit after taxes and employee bonuses. Let’s say the Federal Tax is 17%, the New York State Tax is 12%, and the employee bonus is 10%. However, you want to calculate the employee bonuses on your after-tax profits. Making the very sum you pay in employee bonuses intertwined with the after-tax profits. This is a situation where we can use circular references for iterative calculations in Excel which can be seen in the image below.
As seen in the image above, we find the amount of profit that goes towards federal and state tax by multiplying the percentage by the $500,000 starting profit. From there we now want to find the employee bonuses but we want to take that from the profit after taxes and so our formula for that is the employee bonus percent by our final cell for profits after tax and bonuses. Then our profit after taxes and bonuses is calculated by subtracting the tax amounts and bonus amount from the starting profit. This is where we see a circular reference, the formula for profits after taxes and bonuses (in cell D7) refers to cell D6 (our employee bonus). Cell D6 then has a formula that refers to cell D7. In short; D6 depends on D7, and D7 depends on D6. Thus, circular. By allowing iterative calculations, we instead allow Excel to iteratively (imagine a loop) move towards a stable solution for employee bonuses and the profit of the business after taxes and bonuses.
Go to the page LiveFlow‘s How to Guides to find more information about Excel and Google Sheets formulas and tips that were not covered here.
