--- title: "Workbook calculation" url: https://recursiveai.org/onedocument/docs/spreadsheets-calc/ description: "Namespace RecursiveAI.OneDocument.Spreadsheets.Calc, in the .Spreadsheets package, over the evaluator in calc.md. Entry points: Workbook.Recalculate…" version: "1.0.0" source: https://github.com/recursive-ai-oss/onedocument-csharp/blob/a85720e075c783fc596641d8586ee126cae088c5/docs/spreadsheets-calc.md --- # Workbook calculation Namespace `RecursiveAI.OneDocument.Spreadsheets.Calc`, in the `.Spreadsheets` package, over the evaluator in `calc.md`. Entry points: `Workbook.Recalculate`, `Workbook.RecalculateAll`, `Cell.SetDynamicArrayFormula`, and `onedocument recalc`. Excel is the specification. Statements below marked *(pending the Excel check)* are first readings: the workbooks that settle them are generated by `tools/CalcSweep` (`gen`), have been run through the evaluator here, and are to be recalculated by Excel and compared with `CalcSweep compare` at the release gate; none of them has been compared with Excel yet. ## When it runs Opening never calculates. `Recalculate()` is the explicit command and calculates whatever the calculation mode says: a workbook set to manual stays manual (the `calcPr` is not touched) and the call is "calculate now". Edits made through `Cell` (values, formulas, clearing) are noticed; a plain `Cell.Formula` edit still sets `fullCalcOnLoad` as before and nothing is calculated until you ask. The first call builds the dependency graph and calculates everything. Later calls calculate the formulas an edit made dirty, plus the volatile and the run-time-reference ones. `RecalcOptions.Full` (or `RecalculateAll`) calculates everything. ## The graph One node per formula cell (shared formulas are translated from their anchor; data tables are left alone). The reverse index maps a cell to the formulas that read it: - a block of up to 16 cells is stored as one entry per cell; a bigger block as one entry per column (rows `first..last`), found by a binary search and a running maximum; a block wider than 256 columns sits in a short list. `SUM(A1:A100000)` is one entry. - defined names are expanded: a formula using a name depends on what the name's formula reads (a name holding a lambda reads nothing, its calls' arguments are walked). Sheet-scoped names hide workbook names on their sheet. - table references become areas at build time; `[#This Row]` is the cell on the formula's own row. - 3-D references depend on the cell on every sheet between the two (workbook order, either way round). - whole-row and whole-column references are blocks; when read they are clipped to the sheet's used extent (`ICalcResolver.ClipArea`), so `SUM(A:A)` reads the used rows only. `ROWS(A:A)` still answers 1,048,576 because it asks the reference, not the values; a function that inspects the *shape of the array* made from a whole column sees the used extent. - references to other workbooks add no edges: they are answered from the file's cache or a resolver (see "Other workbooks"). A formula that reads one is marked, so it is calculated again whenever a resolver is supplied. A formula edit, a sheet/name/table change or a different function registry rebuilds the graph on the next call (a pass over the formulas, no evaluation); a value edit costs only its dependents. After a rebuild only the edited cells' dependents are recalculated, unless a sheet, name or table changed, which recalculates everything. ## Levels and threads Formulas whose inputs are all finished form a level. A level runs on all the cores (`RecalcOptions.MaxDegreeOfParallelism`, default one per processor; levels smaller than `ParallelThreshold` run on the calling thread); results are written to the grid afterwards, sequentially, in cell order. Formulas on one level never read each other, so the answers do not depend on the thread count (tested at 1, 2 and 8). Volatile functions give different answers per call by nature. A formula that builds references at run time (a function marked `FunctionTraits.DynamicReference`: `INDIRECT`, `OFFSET`) cannot name its inputs in advance. Such formulas run alone, after the other formulas of their level, and when one reads a formula cell that has not been calculated yet, that cell is calculated first, inline (nesting limit 200). The dynamic ones and the volatile ones are dirty on every call. ## Circular references When no formula is ready but some are waiting, what is left is cycles and the formulas below them. The formulas on a cycle (strongly connected sets, and a formula that reads itself) get the value 0 and a `calc.circular-reference` defect; the formulas below them then calculate from those zeros. With iterative calculation on (below) the cycle is iterated instead. *(pending the Excel check: that Excel shows 0 for every cell on the cycle and calculates the others from it; workbook `circular`)* ## Iterative calculation `calcPr iterate` turns it on, `iterateCount` (default 100, at most 32767) and `iterateDelta` (default 0.001) bound it (ECMA-376 Part 1 §18.2.2). `Workbook.IsIterativeCalculation`, `IterationCount` and `IterationDelta` read them and `SetIterativeCalculation` writes them. Off, a cycle is cut to 0 as above. When nothing is ready but cycles remain, each cycle (a strongly connected set, or a formula reading itself) whose inputs from outside itself are finished is iterated, so a cycle that feeds another is settled first. A round calculates the cycle's formulas once each, in cell order (sheet, row, column), and each formula sees what the earlier ones of the same round wrote. The first round starts from the values the cells hold (the cached values; empty counts as 0). Iteration stops after a round in which no number moved by `iterateDelta` or more (Microsoft: values "change by less than" the maximum change; with a maximum change of 0 only a round with no change at all stops it), or after `iterateCount` rounds. A cell that changes between kinds (number to text, error to number) always counts as moved. A cycle that is still moving after the last round keeps its last values and reports `calc.iteration-limit`. `RecalcResult` counts `IterationRounds`, `IteratedCells` and `UnsettledCycles`. The formulas below a cycle are calculated after it settles. *(pending the Excel check: the round count and stopping rule, the order inside a round, and the values after a cut-off round; workbooks `iterate-self`, `iterate-count`, `iterate-pair-order`)* ## Data tables A `t="dataTable"` formula on the first cell of a block is a what-if table (ECMA-376 Part 1 §18.3.1.40): `ref` is the block of results, `dt2D` marks a two-variable table, `dtr` says a one-variable table has its input values in a row (else in a column), `r1` is the first input cell and `r2` the second of a two-variable table, `del1`/`del2` mean an input cell was deleted. The formulas sit outside the block: the row above it for a column-oriented table, the column to its left for a row-oriented one, the corner cell above-left for a two-variable table. A table with no room for them (block on row 1 or column A), or whose `ref` does not parse, is left alone. Each result cell is computed by giving the input cell the value from the table's edge and calculating the formulas that depend on the input, in dependency order, in an overlay the resolver consults first: the grid is never written with the substituted values, so nothing leaks into other formulas, and the table's own cells are the only cells written. In a two-variable table `r1` takes the values of the top row and `r2` those of the left column. A deleted input gives `#REF!` in every cell. Tables are calculated after the formulas, and formulas that read table cells follow in a further pass. A recalculation runs them when something was calculated or edited. Formulas that build references at run time (`INDIRECT`, `OFFSET`) are not followed from the input cell. *(pending the Excel check: which of `r1`/`r2` takes the top row, the corner position, one-variable tables with several formulas; workbooks `datatable-column`, `datatable-row`, `datatable-2d`)* ## Goal Seek `Workbook.GoalSeek(setCell, toValue, changingCell, options)` returns a `GoalSeekResult` (status `Found`, `NotFound`, `SetCellNotFormula`, `ChangingCellIsFormula` or `SetCellNotNumeric`; iterations; the value tried last or best). It calculates the workbook first, then searches in the same overlay as data tables, so the grid is untouched until the end. The search is a secant iteration from the changing cell's value; it stops when the formula cell is within the maximum change of the target (`iterateDelta`, default 0.001) or after `iterateCount` evaluations (default 100): Microsoft documents Goal Seek as using those two options. A found value is written to the changing cell and the workbook recalculated unless `Apply` is false; a failed search changes nothing. Excel does not document its search method, so the iteration count for a given problem is ours. Formulas reached through `INDIRECT` or `OFFSET` are not followed. ## Other workbooks `externalLink` parts are read into `Workbook.ExternalLinks` (index, target as recorded, sheet names, cached cells from `sheetDataSet`); `[1]Sheet1!A1` in a formula is link 1 (ECMA-376 Part 1 §18.14). A part that is missing or malformed is the defect `xlsx.external-link-unreadable`, and the link keeps its number with no cache. Default-deny: with no resolver nothing outside the file is opened, and a reference reads the cache. A single cell must be in the cache to answer; a block answers with the cached cells it has and blank for the rest, unless it has none, which is `#REF!`. A link or sheet the file does not list, a 3-D external reference and an external defined name are `#REF!`. Whole-column and whole-row blocks are cut to the cached extent. `RecalcOptions.ExternalWorkbooks` supplies an `IExternalWorkbookResolver`: it is asked once per link per calculation for a `Workbook`, and the values read are that workbook's stored values (it is not recalculated). A resolver that returns null, or throws `OfficeException` or `IOException` (reported as `calc.external-resolve-failed`), leaves the cache in charge. Values read live replace the link's cache, and the part's `sheetDataSet` is rewritten at the end of the calculation (the rest of the part is untouched); without a resolver the part is never rewritten. The resolver owns the workbooks it returns. `RecalcResult.ExternalResolved` and `ExternalMissing` count the reads. *(pending the Excel check: blank cells in cached blocks, what Excel writes for blank cached cells, `#REF!` for a cell its cache lacks; workbook `external-cached`)* ## Web functions `WEBSERVICE` and `FILTERXML` are registered and answer only through `RecalcOptions.WebServices` (`IWebServiceResolver`). `WEBSERVICE` gives `#VALUE!` for an address that is empty, over 2048 characters or not http/https, and for a response over 32767 characters (Microsoft's `WEBSERVICE` page). Without a resolver both give Excel's offline result `#VALUE!`, and a cell whose formula called one keeps its cached value when it has one (nothing is overwritten with `#VALUE!`). With a resolver that returns null the function gives `#VALUE!` as usual. *(pending the Excel check: that Excel keeps cached values for offline calls rather than showing `#VALUE!`; workbook `web-offline`)* ## Dynamic arrays and spills A formula is a dynamic array when it is stored as an array formula (`t="array"`) on a cell with the dynamic-array metadata (`cm`, [MS-XLSX] CT_DynamicArrayProperties). `Cell.SetDynamicArrayFormula` writes that form (and adds `xl/metadata.xml` when the workbook has none; a workbook whose metadata part has no dynamic-array entry is refused). Array formulas without `cm` are legacy: they fill the range they name and pad a smaller result with `#N/A`. Plain formulas get implicit intersection. A dynamic result larger than one cell is written into the grid: the anchor keeps the formula and `ref` (the spilled range), the other cells hold plain cached values, which is how Excel stores a spill. It is `#SPILL!` (and `ref` shrinks to the anchor) when a cell in the range other than the formula's own earlier spill holds a value or formula, when it touches a merged range or a table, or when it runs past the last row or column. A `#SPILL!` formula is tried again on every call, and so is a formula whose spill range was edited. If two spills overlap, the one calculated first (cell order within a level, lower levels first) wins. `A1#` reads the spill range of the anchor at `A1` (`#REF!` when `A1` is not a dynamic-array formula), and formulas that read spilled cells follow a changed spill in a further pass (at most 8). *(pending the Excel check: every blocker kind, the overlap rule, `A1#` on a one-cell result; workbooks `spill-*`)* ## After a calculation Cached values in the store are updated; formulas whose result did not change are not rewritten. A text result is kept as a formula string result. Saving writes them as it writes any edit. `fullCalcOnLoad` and the calculation chain are handled as they are for any formula edit. If a call is cancelled, some cells hold new values and some old ones; call again to finish. ## Tools `onedocument recalc [-o out] [--full] [--threads n]` calculates and saves (in place when no `-o`). `tools/CalcSweep PATH...` is the cached-value harness: it remembers every formula's stored value, calculates everything, and compares (numbers at 15 significant digits, text, booleans and errors exactly), classifying each difference as `unimplemented` (the formula calls a function the registry lacks), `external`, `downstream-or-bug` (the workbook has an unimplemented function elsewhere) or `bug`. The figure to quote is matches over the formulas whose functions are all implemented. `CalcSweep gen DIR` writes the Excel test workbooks, `CalcSweep compare ANSWERS` compares them with the oracle's cells-mode answers. ## Not here yet Pivot refresh, `NOW`/`TODAY` clocks, and the function families beyond the evaluator's seed set (a formula calling a missing function evaluates to `#NAME?`). ## Deviations - A whole-column reference as an array has the used extent's shape (above). - A relative reference inside a defined name is read as written, from cell A1.