Calculate circular references as Excel does: turn on iterative calculation, set the iteration limit and maximum change, and save it in XLSX files.On this page
Loading documentation…
Enable iterative calculation to let a circular formula run until its value settles.
A circular reference is a formula that reads its own result, directly or
through other formulas. By default, Sheetwrite shows #CYCLE! in each cell of
the cycle. Excel has the same default. Turn on iterative calculation to
calculate the cycle again and again until its values settle.
Turn it on
Iterative calculation is a workbook setting. Commit it as a document operation:
Calculate circular references. When false, they show #CYCLE!.
maxIterations
100
An integer, 1 to 32,767
The maximum number of passes over one circular reference.
maxChange
0.001
A finite number, 0 or more
Stop when no value in the cycle changes by more than this amount.
The defaults are the Excel defaults. The operation is undoable and goes through
collaboration like other document operations. A change of the setting
recalculates every formula. Values outside the ranges are rejected.
How a cycle calculates
Each recalculation starts from the values that the cells show now. A cell that
showed #CYCLE! starts at 0. The engine then calculates the cells of the
cycle in row order. Each cell reads the newest values of the other cells. The
engine stops after maxIterations passes, or after the first pass in which
no value changes by more than maxChange.
A cycle that does not settle keeps the values of its last pass. It does not
show an error. Excel does the same.
Formulas that read a cycle calculate once, after the cycle settles.
Only numbers have a distance. A text or error value that changes keeps the
calculation going until maxIterations.
OFFSET, INDIRECT, and volatile functions such as NOW work inside a
cycle. Runtime references can add members to a cycle as it calculates.
Downstream readers wait until the cycle settles.
Examples
A running total. Each recalculation adds maxIterations times B1 to the
value that A1 shows:
Running total
A1: =A1+B1
Interest on a balance that includes the interest. With A1 = 1000 and
B1 = 0.1, C1 settles at 1000 × 0.1 / (1 − 0.1) ≈ 111.11:
Interest on the balance
C1: =B1*(A1+C1)
Newton's method for the square root of 2. Each pass moves A1 closer to
1.41421356:
Square root of 2
A1: =IF(A1=0,1,A1-(A1^2-2)/(2*A1))
Snapshots and XLSX files
The setting is in snapshot.workbook.calculation. Workbooks with the default
setting have no calculation field, so their snapshots do not change.
XLSX import reads <calcPr iterate="1" iterateCount="100" iterateDelta="0.001"/>
in workbook.xml. XLSX export writes the same attributes when the setting is
not the default.
Limits
A formula that spills into more than one cell keeps #CYCLE! in a circular
reference.
The engine finds the circular references once per recalculation. Workbooks
without circular references do no extra work. Memory for one recalculation
grows with the number of formulas in its cycles, not with the number of
iterations.
Volatile functions such as RAND inside a cycle draw new values on each
iteration. Such a cycle can reach maxIterations without settling.
const grid: Gridconstgrid:Gridconstgrid:GridThe imperative core grid this controller owns.API reference →Grid.store: StoreinterfaceGrid {store:Store;}interfaceGrid {store:Store;}Store.applyTransaction(tx: Transaction, options?: TransactionApplicationOptions): ApplyTransactionResultStore.applyTransaction(tx: Transaction, options?: TransactionApplicationOptions): ApplyTransactionResultStore.applyTransaction(tx: Transaction, options?: TransactionApplicationOptions): ApplyTransactionResultApply a low-level storage transaction. This bypasses Grid read-only checks and Grid undo/redo history. Use Grid.applyTransaction for normal host-driven edits. Queued and flushed at a barrier — never reentrant.API reference →Transaction.patches: DocumentOp[]interfaceTransaction {patches:DocumentOp[];}interfaceTransaction {patches:DocumentOp[];}Ordered document operations submitted as one store commit.API reference →addr: CellAddresslet addr:CellAddresslet addr:CellAddressAPI reference →CellAddress.sheet: stringinterfaceCellAddress {sheet:string;}interfaceCellAddress {sheet:string;}Sheet identity used by the simple data-first adapter. Defaults to sheet1.API reference →CellAddress.row: numberinterfaceCellAddress {row:number;}interfaceCellAddress {row:number;}0-based row of the cell that holds the top-left corner.API reference →CellAddress.col: numberinterfaceCellAddress {col:number;}interfaceCellAddress {col:number;}0-based column of the cell that holds the top-left corner.API reference →value: CellValuelet value:CellValuelet value:CellValueAPI reference →src: stringlet src:stringlet src:stringcalculation: CalculationSettingslet calculation:CalculationSettingslet calculation:CalculationSettingsIterative calculation options; absent means the defaults.API reference →CalculationSettings.iterate: booleaninterfaceCalculationSettings {iterate:boolean;}interfaceCalculationSettings {iterate:boolean;}Recalculate circular references iteratively instead of showing #CYCLE!; Excel default false.API reference →CalculationSettings.maxIterations: numberinterfaceCalculationSettings {maxIterations:number;}interfaceCalculationSettings {maxIterations:number;}Most passes over each circular reference; an integer from 1 to 32,767, Excel default 100.API reference →CalculationSettings.maxChange: numberinterfaceCalculationSettings {maxChange:number;}interfaceCalculationSettings {maxChange:number;}Iteration stops once no value changes by more than this; finite and >= 0, Excel default 0.001.API reference →function createGrid(host: HTMLElement, opts: GridOptions): GridfunctioncreateGrid(host:HTMLElement,opts:GridOptions,):GridfunctioncreateGrid(host:HTMLElement,opts:GridOptions,):GridCreates and mounts an imperative Grid in the supplied host element.Exampleimport { createGrid, initSheetwrite } from"@sheetwrite/core";awaitinitSheetwrite();consthost= document.createElement("div");host.style.height ="400px";document.body.append(host);constgrid=createGrid(host, {workbook: {activeSheet: "sales",sheets: [{ id: "sales", name: "Sales", rowCount: 2,columns: [{ key: "amount", header: "Amount", type: "number", width: 120 }] }],},data: { rowCount: 2, columns: { amount: [12, 18] } },});grid.scrollToCell({ sheet: "sales", row: 1, col: 0 });// Call when the host view is removed.grid.destroy();See alsoGrid *See alsoGridOptionsAPI reference →function initSheetwrite(source?: BufferSource | URL | string | Request | WebAssembly.Module, engine?: SheetwriteEngine): Promise<void>functioninitSheetwrite(source?:BufferSource|URL|string|Request|WebAssembly.Module,engine?:SheetwriteEngine,):Promise<void>functioninitSheetwrite(source?:|BufferSource|URL|string|Request|WebAssembly.Module,engine?:SheetwriteEngine,):Promise<void>Load one WASM engine before createGrid. The default is @sheetwrite/wasm. Pass the @sheetwrite/formulas module as the second argument to select the full engine.Exampleimport { initSheetwrite } from"@sheetwrite/core";import*as formulas from"@sheetwrite/formulas";awaitinitSheetwrite(undefined, formulas);See also/docs/start/choose-an-engine/See alsocreateGridAPI reference →const host: HTMLElement | nullconsthost:HTMLElement|nullconsthost:HTMLElement|nullvar document: Documentvar document:Documentvar document:Documentwindow.document returns a reference to the document contained in the window. MDN ReferenceAPI reference →ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement | null (+4 overloads)ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement |null (+4 overloads)ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement |null (+4 overloads)Returns the first element that is a descendant of node that matches selectors. MDN Referenceinterface HTMLElementinterfaceHTMLElementinterfaceHTMLElementThe HTMLElement interface represents any HTML element. MDN Referencevar Error: ErrorConstructor
new (message?: string, options?: ErrorOptions) => Error (+2 overloads)var Error:ErrorConstructornew (message?:string, options?:ErrorOptions) =>Error (+2 overloads)var Error:ErrorConstructornew (message?:string, options?:ErrorOptions) =>Error (+2 overloads)const host: HTMLElementconsthost:HTMLElementconsthost:HTMLElementElementCSSInlineStyle.style: CSSStyleDeclarationinterfaceElementCSSInlineStyle {style:CSSStyleDeclaration;}interfaceElementCSSInlineStyle {style:CSSStyleDeclaration;}MDN ReferenceAPI reference →CSSStyleDeclaration.height: stringinterfaceCSSStyleDeclaration {height:string;}interfaceCSSStyleDeclaration {height:string;}MDN ReferenceAPI reference →GridOptions.workbook: WorkbookinterfaceGridOptions {workbook:Workbook;}interfaceGridOptions {workbook:Workbook;}Live workbook schema adopted by the store and updated by document operations.API reference →Workbook.activeSheet: stringinterfaceWorkbook {activeSheet:string;}interfaceWorkbook {activeSheet:string;}Active sheet ID and initial tab presented when the grid is created.API reference →Workbook.sheets: Sheet[]interfaceWorkbook {sheets:Sheet[];}interfaceWorkbook {sheets:Sheet[];}Sheets in display/tab order.API reference →Sheet.id: stringinterfaceSheet {id:string;}interfaceSheet {id:string;}Stable identifier, unique within the workbook and used by every cell address.API reference →Sheet.name: stringinterfaceSheet {name:string;}interfaceSheet {name:string;}User-facing sheet name shown in tabs and workbook exports.API reference →Sheet.rowCount: numberinterfaceSheet {rowCount:number;}interfaceSheet {rowCount:number;}Row count for both in-memory and datasource-backed sheets.API reference →Sheet.columns: Column[]interfaceSheet {columns:Column[];}interfaceSheet {columns:Column[];}Ordered schema; array positions are the zero-based column coordinates.API reference →Column.key: stringinterfaceColumn {key:string;}interfaceColumn {key:string;}Non-empty key, unique within the sheet, used to map input and datasource values.API reference →Column.header: stringinterfaceColumn {header:string;}interfaceColumn {header:string;}Schema label used by table exports and data-grid presentation headers.API reference →Column.type: CellFormatinterfaceColumn {type:CellFormat;}interfaceColumn {type:CellFormat;}Controls cell input parsing and default value formatting for this column.API reference →Column.width: numberinterfaceColumn {width:number;}interfaceColumn {width:number;}Unzoomed column width in CSS pixels.API reference →GridOptions.data?: ColumnarData | undefinedinterfaceGridOptions {data?:ColumnarData|undefined;}interfaceGridOptions {data?:ColumnarData|undefined;}Eager column-major values loaded into workbook.activeSheet; use instead of datasource.API reference →ColumnarData.rowCount: numberinterfaceColumnarData {rowCount:number;}interfaceColumnarData {rowCount:number;}Row count for both in-memory and datasource-backed sheets.API reference →ColumnarData.columns: Record<string, ArrayLike<CellScalar | CellValue>>interfaceColumnarData {columns:Record<string, ArrayLike<CellScalar|CellValue>>;}interfaceColumnarData {columns:Record<string,ArrayLike<CellScalar|CellValue>>;}Exact column runs represented by every returned row.API reference →result: null[]let result:null[]let result:null[]Search state after the replacement (matches re-scanned against the new data).API reference →const store: Storeconststore:Storeconststore:StoreAPI reference →