Established onExcel 365, Windows: calculation option, session scopeGoogle Sheets, web: per-file settingExcel 2016 and later: same option
A cell that depends on itself, directly or through a chain, has no defined value. The software detects it, reports it, and refuses to produce a number.
There is a setting that turns the refusal off.
What the setting does
With iterative calculation enabled, the engine evaluates the loop repeatedly, feeding each pass's result into the next, and stops when either the change between passes falls below a threshold or a maximum number of passes is reached. Both limits are settings.
If the loop converges, this produces the fixed point, which is frequently the right answer. If it does not converge, it produces whatever the last pass happened to yield, and it does so without comment.
Where it is genuinely needed
Financial models with interest on an average balance are the standard case. Interest depends on the balance, the balance depends on the interest, and the relationship is real rather than a mistake. The same shape appears wherever a fee is charged on a total that includes the fee, and in some engineering calculations.
These loops usually converge quickly, and the setting is the straightforward way to solve them without algebra.
What it costs
The detection that would have told you about an accidental loop is now off, for the whole file. A genuine mistake — a total that includes itself, a reference dragged one row too far — no longer produces an error. It produces a number, arrived at by iteration, indistinguishable from the deliberate one.
Which is the exact shape this site is about, achieved by a setting, applied to every cell in the workbook at once.
Where the setting lives
In Excel it is a calculation option that applies to the session rather than only to the file that requested it, so opening a workbook that uses it can change the behaviour of others open at the same time. In Google Sheets it is per-file, in the spreadsheet settings, with the same two limits.
The difference matters when a model is shared: a colleague opening your workbook may be running with your setting, or may not be, and the same file can produce a number for one person and an error for another.
The alternative that keeps the detection
Break the loop by hand. Most financial circularities have a closed-form solution: interest on an average balance can be rearranged into a single expression, and where it cannot, an explicit iteration in a few columns does the same job visibly.
The explicit version is longer and has three advantages. The number of passes is visible rather than a setting. The convergence can be checked, because the difference between the last two passes is a cell. And the detection stays on for the rest of the workbook.
How to find an accidental one
Before enabling the setting, and periodically afterwards, turn it off and see what breaks. Every accidental loop in the file will announce itself at once, and the deliberate ones will too, so the list can be compared against the loops you know about.
Excel also lists the cells involved in circular references in its status bar and under its error-checking menu. That list is only populated while the detection is on, which is the reason for turning it off deliberately rather than waiting to be told.
What to write down
If the setting is on, the file should say so, in a cell, near the top, naming which calculation is circular and why. Otherwise the next person inherits a workbook whose error detection is disabled for a reason nobody can state, and the reasonable thing for them to do is turn it back on, which breaks the model.
What we cannot verify
The setting, its two limits and the behaviour on non-convergence are documented by both vendors. The scope of the Excel setting, session against workbook, is stated in the vendor's documentation and behaves as described on the build we tested; older versions may differ and we did not test them.
What convergence does and does not guarantee
A loop that settles below the change threshold has stopped moving by that much per pass. It has not been shown to have reached the correct answer, only a stable one, and a badly formed loop can be stable at a value that means nothing.
Which is why the explicit version is worth the extra columns wherever the figure matters: the passes are visible, and a person can see whether the sequence is approaching something or sitting still because two errors are cancelling.
The commonest accidental loop
A total placed inside the range it totals. It happens when a row is inserted at the bottom of a block and the sum was already there, or when a sum is copied down one row too far.
With detection on it is reported immediately and fixed in seconds. With the setting on it converges, usually to something close to twice the right answer, which is a plausible number for a total.
In short
- A self-dependent cell has no defined value, and the software says so and stops.
- A setting replaces the refusal with repeated evaluation until a threshold or a pass limit.
- Interest on an average balance is the standard case where the loop is real.
- With the setting on, an accidental loop produces a number rather than an error.
- In one product the setting applies to the session, so the same file can behave differently.
- Turning it off deliberately makes every loop in the file announce itself at once.