Iterative calculation
A circular reference is normally an error, and every cell in the loop shows #CYCLE!. Some models are circular on purpose: a bonus that is a share of the profit it is taken out of, interest charged on the balance it is part of. Formulas > Calculation Options turns on iterative calculation, and the loop runs until it stops moving or the passes run out. The setting rides in getState() and goes into the .xlsx as calcPr, so the file opens the same way in Excel. (requires @svgrid/enterprise)
A live, editable Svelte 5 data grid example from the SvGrid gallery (Spreadsheet). See the SvGrid documentation for the full API.
About this example
Excel's Enable iterative calculation in a Svelte 5 spreadsheet: a circular reference settles on its fixed point instead of showing #CYCLE!, with a cap on the passes and on the smallest change worth another one. The classic bonus-out-of-profit model and an interest charge on the balance it is part of. Formulas > Calculation Options is the switch, the setting rides in getState() and goes into the .xlsx as calcPr, so the file opens the same way in Excel. Enterprise, in @svgrid/enterprise.
A circular reference is usually a mistake, and the shell says so: every cell in the loop shows #CYCLE! while the rest of the sheet keeps working. Some models are circular on purpose, though, because the answer is a fixed point rather than an error.
The classic one is here. A bonus is a tenth of the profit AFTER the bonus, so the bonus depends on the profit and the profit depends on the bonus. There is one pair of numbers that satisfies both, and Excel finds it by running the loop over and over from the values it last had:
Formulas > Calculation Options Enable iterative calculation, with a cap on the passes and on the smallest change worth another pass.
The reserve model under it is the same shape: interest is charged on a balance the interest is part of.
Try: read B4 and B5 as they stand, both #CYCLE!. Open Formulas > Calculation Options, tick the box and press OK. Change the profit in B2 and watch the bonus follow. Turn it off again and the cycle is an error once more. Save As and open the file in Excel: the setting rides along in calcPr, so the model works there too.
Imports, features and API used
Imports: @svgrid/enterprise
Frequently asked questions
Why is a circular reference an error by default?
Because it almost always is one: a total that includes itself, a formula dragged one row too far. The shell marks every cell in the loop #CYCLE! and leaves the rest of the sheet working, which is what Excel does. Iterative calculation is the opt-in for the models where the loop is meant.
What do the two limits mean?
Maximum iterations is how many passes over the loop before the answer is taken as it stands, 100 by default. Maximum change is how small a move counts as settled, 0.001 by default, so a converging model stops early rather than burning every pass. Neither promises convergence; together they promise the sheet stops.
Does the setting survive a save?
Yes. It rides in getState() with the workbook, and it goes into the .xlsx as calcPr with iterate, iterateCount and iterateDelta, which is where Excel keeps it. A file saved with iteration on opens with it on.
Related documentation
Related articles
- A Fill Handle (Drag to Fill) in SvGrid - Build a working spreadsheet-style fill handle on top of SvGrid's cell selection and editing - pointer tracking, range highlighting, series fill, and undo/redo integration all covered.
- Pivot Tables in Svelte - Summarize Data Without a Spreadsheet - Run a cross-tab pivot directly inside your Svelte app using @svgrid/enterprise createPivotModel - no Excel, no server-side aggregation, no stale exports.
- Spreadsheet-Style Cell Range Selection in SvGrid - How to enable drag-to-select cell ranges, read live selection state, and build a status-bar footer that sums and averages the selected values.
Source code (492-sheet-iterative.svelte)
<script lang="ts">
/**
* 482. Iterative calculation
* --------------------------
* A circular reference is usually a mistake, and the shell says so: every
* cell in the loop shows #CYCLE! while the rest of the sheet keeps
* working. Some models are circular on purpose, though, because the answer
* is a fixed point rather than an error.
*
* The classic one is here. A bonus is a tenth of the profit AFTER the
* bonus, so the bonus depends on the profit and the profit depends on the
* bonus. There is one pair of numbers that satisfies both, and Excel finds
* it by running the loop over and over from the values it last had:
*
* Formulas > Calculation Options Enable iterative calculation, with a
* cap on the passes and on the smallest
* change worth another pass.
*
* The reserve model under it is the same shape: interest is charged on a
* balance the interest is part of.
*
* Try: read B4 and B5 as they stand, both #CYCLE!. Open Formulas >
* Calculation Options, tick the box and press OK. Change the profit in B2
* and watch the bonus follow. Turn it off again and the cycle is an error
* once more. Save As and open the file in Excel: the setting rides along
* in calcPr, so the model works there too.
*/
import { SvSheet, createWorkbook, createSheetDocument } from '@svgrid/enterprise'
const rows: string[][] = [
['A bonus taken out of the profit it is a share of', '', ''],
['Profit before bonus', '900000', ''],
['Bonus rate', '0.1', ''],
['Bonus', '=B3*B5', 'a tenth of the profit after the bonus'],
['Profit after bonus', '=B2-B4', 'which is what the bonus comes out of'],
['', '', ''],
['Interest charged on the balance it is part of', '', ''],
['Opening balance', '250000', ''],
['Rate', '0.07', ''],
['Interest', '=B11*B9', 'charged on the closing balance'],
['Closing balance', '=B8+B10', 'which the interest is part of'],
]
// Iteration is off to begin with, exactly as Excel opens: the cycle is an
// error until someone says it is meant. Formulas > Calculation Options is
// the switch, and `setIteration` is the same thing from code.
const wb = createWorkbook([{ name: 'Model', cells: rows }])
const doc = createSheetDocument({ workbook: wb })
const sheet = doc.get('Model')
const at = { rowIdAt: (i: number) => `r${i}`, columnIdAt: (i: number) => String.fromCharCode(65 + i) }
for (const r of [0, 6]) sheet.formats.set([[r, 0, r, 2]], { bold: true, fill: '#e2e8f0', color: '#0f172a' }, at)
sheet.formats.set([[1, 1, 1, 1]], { numFmt: '$#,##0' }, at)
sheet.formats.set([[3, 1, 4, 1]], { numFmt: '$#,##0', bold: true }, at)
sheet.formats.set([[7, 1, 7, 1]], { numFmt: '$#,##0' }, at)
sheet.formats.set([[9, 1, 10, 1]], { numFmt: '$#,##0', bold: true }, at)
sheet.formats.set([[2, 1, 2, 1]], { numFmt: '0.0%' }, at)
sheet.formats.set([[8, 1, 8, 1]], { numFmt: '0.0%' }, at)
sheet.formats.set([[0, 2, 10, 2]], { color: '#64748b' }, at)
sheet.widths.A = 250
sheet.widths.B = 160
sheet.widths.C = 290
</script>
<SvSheet document={doc} height="100%" rows={16} columns={6} />More Spreadsheet examples
- Spreadsheet + Ribbon bar - The whole Excel surface as one component, <SvSheet workbook={wb} />: a six-tab ribbon (Home, Insert, Formulas, Data, Review, View), the Name Box and fx bar, sheet tabs and the Sum / Average / Count status bar. Format cells, merge them, comment on them, validate what goes in, colour them by rule, filter the region, protect the sheet; every button is the same call as its shortcut, so Ctrl+B and the Bold button cannot drift. A two-sheet P&L with the formats travelling in the document.
- Spreadsheet + formulas - Real formula engine inside the grid: cell refs (A1), ranges (A1:A10), SUM / AVG / IF / COUNTIF / ROUND, arithmetic, string concat, cycle detection.
- Per-cell custom borders (KPI) - Editable KPI scorecard. spreadsheetLayout paints spreadsheet-style per-edge custom borders via an absolute-positioned overlay (no border-collapse conflicts). Edit any quarter or target - the borders re-derive: green double = beat target, blue solid = hit, amber dotted = near miss, red dashed = bad miss; row champion gets a colored full frame.
- Cell merging (spreadsheet shell) - A real invoice rendered on an Excel-style shell: A / B / C / D / E column letters across the top, row numbers down the left. Brand band, bill-from / bill-to address blocks, meta block, line items, totals, notes, signatures - all assembled from MergeSpec + CellBorderSpec. Editable Qty / Rate / addresses / notes; totals recompute live.
- HyperFormula integration - Full HyperFormula engine wired into the grid as a peer-optional dep. Editable spreadsheet with A1-style cell refs, dozens of formulas across math (SUM / SUMIF), lookup (VLOOKUP / INDEX-MATCH), text (CONCAT / UPPER), date (TODAY / DATEDIF), logical (IF nests), financial (PMT / IRR / NPV), statistical (AVERAGE / MAX / RANK).