Iterative calculation

Iterative calculation allows circular references to converge over repeated evaluations, similar to Excel. This is useful for finance models where the result depends on its own output.

Enable it

const state = useSpreadsheetState({
  iterativeCalculation: {
    enabled: true,
    maxIterations: 100,
    maxChange: 0.001,
  },
});

Notes:

• Defaults match Excel (100 iterations, 0.001 max change).

• When disabled, circular references return #REF!.

• When enabled, non-converging formulas return #NUM!.

Supported modes

• Single-threaded: useCalculation runs iterative loops on the UI thread.

• Worker mode: useCalculationWorker delegates iterative groups to the worker.

Example formulas to test

Converging loop:

// A1
0
// A1 (replace)
=(A1+10)/2

Expected: A1 converges to ~10.

Non-converging loop:

// A2
0
// A2 (replace)
=A2+1

Expected: A2 returns #NUM!.

Testing

Unit tests for single-threaded iterative calculation:

yarn workspace @rowsncolumns/spreadsheet-state test -- use-calculation.spec.ts

Worker iterative tests:

yarn workspace @rowsncolumns/calculation-worker test