Iterative calculation, friend or foe?

Among the infinite number of settings in Excel, one innocent looking option fundamentally changes the way Excel calculates formula results. It goes by the name Iterative calculation. This little known feature has its uses, but it doesn’t come without risk. Let’s explore what it does, how it can be used, and what should be considered before enabling it.

How Excel calculates

As anyone who’s been using Excel knows, formula results are recalculated automatically whenever something changes in one of the cells they reference. If the referenced cells contain formulas too, Excel will dutifully calculate those first. This way, even in a modest spreadsheet model, just a single cell edit can cause a whole chain of formulas to be recalculated. Very useful!

So what happens when, in this chain of formulas, a formula is found that has already been encountered? Take the simple case in which a formula in cell A1 references cell B1, and cell B1 contains a formula that makes a reference back to cell A1. What should Excel do? It’s like the old joke in the dictionary:

What Excel should do very much depends on what was intended. Often, circular references have slipped in by accident. In such cases the formulas must simply be reworked. But what to do if you want a circular dependency?

Isn’t a circular reference always a mistake?

A circular dependency might seem like a counterintuitive requirement. Yet the need to backtrack to a previous result arises all too easily, the most commonly cited example being a compound interest calculation.

Try it and Excel will complain. The dreaded “we found one or more circular references” message is infamous among Excel users, especially when they are unintentional. Yet Excel’s protest is justified: if it just continued to figure out the answer, it would be calculating forever. Ideally, if the circular dependency was intentional, you’d want some way to tell it to stop after a while. Meet iterative calculation.

With the iterative calculation option turned on, you can specify how many times (iterations) Excel runs through a formula chain to calculate a result, usually so as to converge on an outcome of acceptable precision.

Iterative calculation: a simple use case

Here’s an interesting use case: we’ll create a table with two columns where the second column automatically inserts a timestamp whenever something is added, a kind of ‘creation date’ for table rows.

Note that a regular Excel formula cannot do this. If you tried something like IF(ISBLANK(A1), “”, NOW()), the timestamp would always update even if one had been previously generated. All timestamps would always reflect the latest edit:

Rather, the formula must do the following:

If the formula cell already contains a timestamp, leave it.

If there is no timestamp yet, check to see if one must be created.

Without iterative calculation, step 1 would be impossible. So after turning on File > Options > Formulas > Enable iterative calculation, enter the following formula in column B:B:

=IF(AND(B1<>"", B1<>0), B1, IF(ISBLANK(A1), "", NOW()))

Also make sure to set the Number Format to Time:

That’s it! Try to add some data to the columns A-C and watch timestamps being added. Once created, timestamps should not change.

The caveat of using iterative calculation

Since accidental circular references are a common source of headaches in the Excel world, some might be tempted to turn on iterative calculation to keep a spreadsheet working without having to fix the root of the problem. It should go without saying that this is not good practice.

Even when iterative calculation is enabled on purpose, it doesn’t come without risk. Not everyone is familiar with the option, and even those who know the option exists might not be aware it is turned on. After all, most spreadsheets don’t require iterative calculation.

So what’s the risk? Although iterative calculation allows you to set up circular dependencies on purpose, it also masks any unintentional ones. If you must use iterative calculation, make sure the behaviour is well-documented and that the model is thoroughly checked for inadvertent calculation loops.

Tip: Helpful tools like the PerfectXL Risk Finder can check for calculation loops even when iterative calculation is enabled.