# Iterative calculation

Use iterative calculation to resolve circular references in your spreadsheet formulas.

# What is iterative calculation?

A circular reference occurs when a formula refers back to its own cell, either directly or through a chain of other cells. For example:

Direct: A1 contains =A1+1
Indirect: A1 contains =B1+1 and B1 contains =A1+1

By default, HyperFormula returns a #CYCLE! error for circular references. However, some spreadsheet models intentionally use circular references for iterative calculations, such as:

Financial modeling: calculating loan payments where interest depends on the balance
Goal seeking: finding values that satisfy a target condition
Numerical methods: implementing Newton-Raphson or other iterative algorithms
Feedback systems: modeling systems where outputs influence inputs

When iterative calculation is enabled, HyperFormula repeatedly evaluates the circular formulas until the values stabilize (converge) or a maximum number of iterations is reached.

# How it works

All cells in the cycle are initialized to a starting value (default: 0)
Cells are evaluated in address order (by sheet, then row, then column)
Each cell immediately uses the most recently computed values from other cells
After each complete pass, HyperFormula checks if all values have converged
Iteration stops when values converge or the maximum iterations are reached

# Convergence

A cell is considered converged when:

Numeric values: the change between iterations is below the configured threshold: |new - old| < threshold
Non-numeric values (strings, booleans, errors): the value stops changing between iterations

All cells in the cycle must converge for iteration to stop early.

# Enabling iterative calculation

To enable iterative calculation, set the enableIterativeCalculation option to true:

const hfInstance = HyperFormula.buildFromArray(
  [
    ['=A1+1'],  // A1 references itself
  ],
  {
    licenseKey: 'gpl-v3',
    enableIterativeCalculation: true,
  }
);

// With default settings (100 iterations, threshold 0.001, initial value 0):
// A1 will equal 100 after iteration completes
console.log(hfInstance.getCellValue({ sheet: 0, row: 0, col: 0 })); // 100

TIP

Iterative calculation settings are configured at engine initialization and apply to all sheets.

# Configuration options

Option	Type	Default	Description
`enableIterativeCalculation`	`boolean`	`false`	Enable iterative calculation for circular references
`iterativeCalculationLimit`	`number`	`100`	Maximum number of iterations before stopping
`iterativeCalculationThreshold`	`number`	`0.001`	Values must change by less than this to be considered converged
`iterativeCalculationInitialValue`	`number \\| string \\| boolean \\| Date`	`0`	Starting value for cells in circular references

# Example

Two cells that converge to a stable value:

const hf = HyperFormula.buildFromArray(
  [
    ['=B1/2 + 1', '=A1/2 + 1'],  // A1 and B1 reference each other
  ],
  {
    licenseKey: 'gpl-v3',
    enableIterativeCalculation: true,
    iterativeCalculationThreshold: 0.0001,
  }
);

// Both cells converge to 2
console.log(hf.getCellValue({ sheet: 0, row: 0, col: 0 })); // ~2 (A1)
console.log(hf.getCellValue({ sheet: 0, row: 0, col: 1 })); // ~2 (B1)

# Behavior notes

# Non-converging formulas

Some formulas never converge (e.g., =A1+1 always increases). In these cases, iteration runs until iterativeCalculationLimit is reached, and the final value is used.

# Error handling

If a formula in the cycle produces an error during iteration (e.g., #DIV/0!), the error becomes the cell's final value. Cells depending on error values will propagate the error.

# Ranges containing cycles

If a range reference (e.g., SUM(A1:A10)) includes cells that are part of a cycle, the range is recalculated on each iteration to reflect the updated values.

# Recalculation behavior

When any cell in a circular reference is modified, the entire cycle is re-evaluated from the initial value. Previous converged values are not preserved between recalculations.

← Array formulas Internationalization features →