Recursive AI

Calculate circular Excel formulas in C#

OneDocument is a free, open-source Word, Excel and PowerPoint document library for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

A circular formula reads itself. Iteration lets the calculation repeat within a limit.

Terminal
dotnet add package RecursiveAI.OneDocument.Spreadsheets

Requires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.

C#
using RecursiveAI.OneDocument.Spreadsheets;
using var book = Workbook.Create();
var cell = book.Sheets[0]["A1"];
cell.Formula = "(A1+10)/2";
book.SetIterativeCalculation(true, 20, 0.01);
var result = book.Recalculate();
Console.WriteLine(cell.Number);
Console.WriteLine(result.IterationRounds);

Unzip the example, then dotnet run.

Prints the value in A1 and the number of rounds (with an English-US decimal separator):

Text
9.990234375
10

The full program

The download evaluates the same formula with iteration off and then on. RecalculateAll() reruns the formula even though its text hasn’t changed.

C#
using RecursiveAI.OneDocument.Spreadsheets;
 
using var workbook = Workbook.Create();
var cell = workbook.Sheets[0]["A1"];
cell.Formula = "(A1+10)/2";
 
var off = workbook.Recalculate();
Console.WriteLine($"Off: A1 = {cell.Number}, circular cells = {off.CircularCells}");
 
workbook.SetIterativeCalculation(true, 20, 0.01);
var on = workbook.RecalculateAll();
Console.WriteLine(FormattableString.Invariant($"On: A1 = {cell.Number}"));
Console.WriteLine($"Rounds: {on.IterationRounds}");
Console.WriteLine($"Unsettled cycles: {on.UnsettledCycles}");
Text
Off: A1 = 0, circular cells = 1
On: A1 = 9.990234375
Rounds: 10
Unsettled cycles: 0

If a cycle is still changing when the round limit is reached, OneDocument keeps its last values and records calc.iteration-limit in workbook.Defects; check UnsettledCycles before using a result that needs to settle.

Variations

To see what hitting the limit looks like, replace the settings line in the full program. This permits three rounds, still using 0.01 as the change threshold:

C#
workbook.SetIterativeCalculation(true, 3, 0.01);

The iteration-on part now prints:

Text
On: A1 = 8.75
Rounds: 3
Unsettled cycles: 1

The short answer

Turn iteration on with Workbook.SetIterativeCalculation(true, maxIterations, maxChange), then call Recalculate(). Read the calculated value from the cell and the round count from the result. A result can have a numeric value even when iteration ran out of rounds, so check UnsettledCycles too.

How do I turn on iterative calculation for an Excel workbook in C#?

SetIterativeCalculation writes the workbook settings; it doesn’t do any calculation by itself. Opening a workbook doesn’t calculate it either. Use Recalculate() to evaluate formulas on demand. Use RecalculateAll() to force a fresh evaluation of all formulas when comparing settings as in the download. These settings are the workbook counterparts of the options Microsoft calls Maximum Iterations and Maximum Change.

How do I calculate a circular reference in Excel without an endless loop?

With (A1+10)/2, the first three values starting from zero are 5, 7.5 and 8.75: each round halves the remaining distance to 10. In OneDocument the round starts from the cached value, or zero for an empty cell. The change threshold is the difference between rounds, not the distance to the exact mathematical answer. The snippet permits up to 20 rounds but stops after 10 because the tenth round changed the value by less than 0.01. A threshold of zero instead requires no change at all.

IterationRounds is summed across cycles. It equals the number of passes through A1 here only because there is one cycle. For multiple cycles, use UnsettledCycles to see if any hit the limit while still changing. The calculation docs describe OneDocument’s order within a round; don’t assume the example’s round count will match every spreadsheet application or cached starting value.

Should I enable iterative calculation for an accidental circular reference?

Usually not. If a total accidentally includes its own cell, narrow the sum range instead of asking the workbook to iterate. With iteration off, OneDocument gives cells on a cycle the value zero and records calc.circular-reference in workbook.Defects; formulas that depend on the cycle then use those zeros. The zero in the download’s first line is therefore not a solution. Turn iteration on when the feedback is intentional and you know how to judge the resulting value.

All onedocument recipes