Circular references can be troublesome to troubleshoot and even more difficult to solve.
A circular reference refers to a specific formula construction in spreadsheets like Microsoft Excel and Google Sheets that occurs when a formula in a cell refers back to that same cell or refers to other cells that depend on the original cell.
While most of the time the way to fix a circular refence is to remove it, or replace it with new formulas using algebra to remove the need for the circular reference, there are instances where circular references are unavoidable and iterative calculations are required to do the calculation. Iterative calculations is a feature built into spreadsheet programs that can calculate the results of formulas using circular references by running the calculation multiple times until the results converge to a stable value. Excel and Google Sheets both offer this feature, and it is easy to turn on.
In Microsoft Excel, you can turn on iterative calculations by going through the menu:
Excel > Preferences > Calculation > Enable iterative calculation (PC) or Use iterative calculation (Mac), check the box
In Google Sheets, you can turn on iterative calculations by going through the menu:
File > Settings > Calculation > Iterative Calculation (select on)
But often the practical limitations of spreadsheet software make the results of iterative calculations unreliable, as the spreadsheet may fail to converge on a stable value, recalculating a different result each time. This can be because of poorly constructed formulas, multiple values that solve the circular, or inherent limitations in the math used by iteratative calculations. Here are some strategies to help deal with problematic iterative calculations.
Use conditional logic to prevent extreme or invalid values from going through iterations.
For instance, if a formula risks producing division by zero, wrap the formula in an IF statement that returns a default value (often 0) when the denominator is 0, or use IFERROR to return a default value when the formula errors.
Apply MIN and MAX functions to constrain values within a reasonable range.
This prevents calculations from producing unrealistic results. For example ...
=MAX(MIN(A1 * B1, UpperLimit), LowerLimit)
... would ensure that the calculated result remains bounded between the LowerLimit and UpperLimit you identify.
Adjust calculation precision to improve reliability.
Rounding intermediate results can improve reliability, especially when small differences cause large swings in subsequent iterations. For example ...
=ROUND(A1 * B1, 2)
would round the intermediate result to 2 decimal places, which can help prevent large swings in subsequent iterations.
Adjust the precision settings for the iterative calculations.
Both Excel and Google Sheets have two precision settings to allow you to set the maximum number of iterations (simply, the max number of times the calculations will run) and the maximum change (the maximum amount the calculation can change between iterations). Changing these settings to more iterations or a smaller maximum change can help spreadsheets converge on a stable solution (meaning, the result will not change when the spreadsheet recalculates).
Stabilize with helper columns to simplify troubleshooting and improve calculation reliability.
Helper columns are a common technique for making complicated formulas easier to understand and troubleshoot, and can additionally improve calculation reliability in complicated iterative calculations.
Utilize alternative approaches
Alternatively, explore alterative approaches to iterative calculations:
Remove the circular reference and use algebra to solve the problem
Always start here. But sometimes circular references are either (a) unavoidable or (b) from a practical perspective, better to use than complicated algebraic formulas that could be hard to troubleshoot or edit. SAFE conversions and exit waterfalls are examples in my world where iterative calculations are often used without issue.
Use spreadsheet tools like Goal Seek or Solver instead of iterative calculations
Goal Seek is a tool prebuilt into Excel and available as an add-in for Google Sheets that allows you to find a calculate a desired output (the goal you input) by iteratively changing an input (the seek the spreadsheet calculates). It is easy to setup and use, but note that it is limited to changing a single variable, and that it is a one-time calculation, meaning that the calculated result will not update as other variables change.
Solver is a more powerful add-in for Excel and Google Sheets that can be used to solve more complex problems by changing multiple variables and adding constraints. There are many resources on the web about how to setup and use Solver, and it can be an effective solution for complex problems requiring iterative calculations.
Use VBA for custom iterative calculations
I'm personally not a big user of Visual Basic for Applications (VBA), but it is possible to write custom VBA to automate calculations beyond Excel’s default capabilities.
Having difficulty with iterative calculations? Contact me and let's see if I can help.