---
title: "Calculate circular Excel formulas in C#"
url: https://recursiveai.org/onedocument/cookbook/calculate-circular-excel-formulas/
description: "Enable iterative calculation for a circular Excel formula in C#. Set the maximum change and round count, and see the result with iteration on and off."
version: "1.0.0"
---

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

```shell
dotnet add package RecursiveAI.OneDocument.Spreadsheets
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
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](https://recursiveai.org/onedocument/cookbook/calculate-circular-excel-formulas/calculate-circular-excel-formulas.zip), 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.

```csharp
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:

```csharp
workbook.SetIterativeCalculation(true, 3, 0.01);
```

The iteration-on part now prints:

```text
On: A1 = 8.75
Rounds: 3
Unsettled cycles: 1
```

## Related recipes

- [Recalculate Excel formulas](https://recursiveai.org/onedocument/cookbook/recalculate-excel-formulas/)
- [Create an Excel file](https://recursiveai.org/onedocument/cookbook/create-excel-file/)

## 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](https://support.microsoft.com/en-us/excel/remove-or-allow-a-circular-reference-in-excel).

## 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](https://recursiveai.org/onedocument/docs/spreadsheets-calc/#iterative-calculation) 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.
