Iterative Calculations in Excel and Google Sheets

Iterative calculation is the setting that lets a spreadsheet solve a formula that depends on its own result. It runs the calculation over and over until the answer stops moving. Here is where to turn it on:

Excel: Excel > Preferences > Calculation > Enable iterative calculation (PC) or Use iterative calculation (Mac), check the box

Google Sheets: File > Settings > Calculation > Iterative calculation, select On

That is the whole answer if you just needed the switch. The rest of this page is for the harder case, which is when you've turned it on and the number still won't settle.

"Circular dependency detected. To resolve with iterative calculation, see File > Settings."

That's the error Google Sheets puts in the cell when a formula refers back to itself. It's telling you two things at once: there's a loop in your formulas, and Sheets has a setting that will attempt to solve it anyway.

Turning the setting on makes the message go away. It doesn't make the loop go away. If the loop was an accident, which it usually is, the honest fix is to find it and remove it, and circular references covers how to track down the cell that's causing it. If the loop is deliberate, and a few of them genuinely are, read on.

When iterative calculation is the right call

Most circular references should be removed, usually by rewriting the formula with algebra so nothing depends on its own output. But some circulars are either unavoidable or, from a practical standpoint, better than the algebraic alternative that nobody will be able to edit six months later. SAFE conversions, exit waterfalls, and interest on a revolving balance are the three I hit most often, and I leave all of them circular.

The catch is that spreadsheets are not always good at this. The sheet can fail to converge, handing you a different result on every recalculation, and it will do that quietly. The cause is usually a poorly constructed formula, sometimes several values that satisfy the circular equally well, and occasionally the limits of the math itself. What follows are the fixes, roughly in the order I try them.

Reduce the number of circular calculations

One approach is simply to limit the number of formulas that rely on circular references. Each additional circular calculation increases the complexity of the iteration process and raises the chance that the spreadsheet will not converge on a stable value. Reducing the computational burden on Excel or Google Sheets by reorganizing the spreadsheet can often solve issues with formulas failing to converge reliably. Options could include:

• Reduce the number of cells that use circular references
• Consolidate the logic using circulars into a single sheet

For example, the exit waterfall in the Cap Table and Exit Waterfall Tool uses iterative calculations for a number of core calculations, and often issues with the calculations failing to converge can be solved by removing any unnecessary prebuilt share classes.

Adjust the precision settings for the iterative calculations.

Both Excel and Google Sheets have two precision settings to allow you to set the:

1. Maximum number of iterations (simply, the max number of times the calculations will run)
2. 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). It will mean that the spreadsheet will take longer to calculate, but it can help the spreadsheet find a stable solution.

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.

The idea behind a helper column is to break down a complex formula in one cell into smaller calculations across multiple cells, which (1) from a spreadsheet best practices perspective can make it easier to understand the troubleshoot the calculation, and (2) can help the spreadsheet calculations converge on a stable solution easier. So instead of condensing a long formula into one cell, break it out into smaller formulas across multiple cells. From a modeler perspective it seems unnecessary, but in practice it can be helpful to make calculations work better.

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. Applying conditional logic to intermediate results can help calculations from going in unintended directions.

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. Similar to the conditional logic, applying these to intermediate results can help calculations converge quicker and more reliably.

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. Applying rounding or reducing the number of significant digits (meaning, if you already use ROUND to 6 significant digits, decreasing it to 2 significant digits)can help the spreadsheet's iterative calculations work better.

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

Visual Basic for Applications (VBA) can automate calculations beyond Excel's default capabilities.