Repository Public
1# Formula engine23This document describes how ORIX Sheet turns formula text into a value: the parser, the abstract syntax4tree (AST), the printer and its locale syntax, the evaluator, the function registry, structured references5and defined names. How formulas are scheduled and recalculated is the subject of6[calculation-graph.md](calculation-graph.md); number semantics are in7[../mathematics/numerical-precision.md](../mathematics/numerical-precision.md); the function catalogue is in8[../compatibility/formulas.md](../compatibility/formulas.md).910Everything below was read in the source files named in each section. Behaviours quoted as11"`formula` → result" were obtained by evaluating the formula with `CalcEngine.evaluate` in a throw-away12executable linked against `Packages/SheetEngine` on 2026-11-21 (debug build); they are observations of the13current code, not statements about Excel unless Excel is named.1415## Where the code lives1617| Piece | File | Lines |18|---|---|---:|19| AST (`Expr`, `RangeExpr`, `StructuredRef`, `FormulaSyntax`, `ParseError`) | `Packages/SheetCore/Sources/SheetCore/Formula/AST.swift` | 156 |20| Parser (`FormulaParser`) | `Packages/SheetCore/Sources/SheetCore/Formula/Parser.swift` | 471 |21| Printer (`FormulaPrinter`, `Expr.formulaText`) | `Packages/SheetCore/Sources/SheetCore/Formula/Printer.swift` | 128 |22| Evaluator (`EvalContext`) | `Packages/SheetCore/Sources/SheetCore/Engine/Evaluator.swift` | 567 |23| Registry (`Operand`, `ParamKind`, `FunctionSpec`, `FunctionRegistry`) | `Packages/SheetCore/Sources/SheetCore/Engine/Registry.swift` | 111 |24| Core built-ins (24 functions, `CompensatedSum`) | `Packages/SheetCore/Sources/SheetCore/Engine/Builtins.swift` | 186 |25| Values (`Value`, `ArrayValue`, `LambdaValue`, `CellError`, `compareValues`) | `Packages/SheetCore/Sources/SheetCore/Model/Value.swift` | 290 |26| Typed input (`InputParser`) | `Packages/SheetCore/Sources/SheetCore/Engine/InputParser.swift` | 189 |27| Reference rewriting (`RefTransform`) | `Packages/SheetCore/Sources/SheetCore/Engine/RefRewrite.swift` | 149 |28| Library bootstrap (`ORIXEngine`) | `Packages/SheetEngine/Sources/SheetEngine/Bootstrap.swift` | 37 |2930Source: `wc -l` on the files, 2026-11-21.3132`SheetCore` has no package dependency. The function libraries (`FunctionsNumeric`, `FunctionsText`,33`FunctionsLookup`) depend on `SheetCore` (and `FunctionsText` on `SheetFormat`); `SheetEngine` depends on all34of them and only contains the bootstrap and the `orix-bench` executable (see the `Package.swift` files).3536## Pipeline3738<!-- diagram: formula-engine-pipeline -->39```mermaid40flowchart TD41 T["Typed text<br/>(cell editor, formula bar, paste)"] --> IP["InputParser<br/>number, date, boolean, text or formula?"]42 F["Formula text from a file<br/>(canonical syntax)"] --> P43 IP -->|starts with = + or -| P["FormulaParser<br/>UTF-8 bytes, precedence climbing"]44 P --> AST["Expr (immutable AST)<br/>stored in Formula.expr"]45 AST --> PR["FormulaPrinter<br/>canonical or locale syntax"]46 AST --> REG["CalcEngine.register<br/>precedents, volatility"]47 AST --> EV["EvalContext.evaluate<br/>returns an Operand"]48 EV --> FN["FunctionRegistry<br/>465 specs + 8 special forms"]49 EV --> V["Value stored in Formula.value<br/>(arrays spill)"]50```51SVG fallback: 5253There is no separate lexer and no token array: `FormulaParser` is a single struct that walks the UTF-8 bytes54of the formula (`private let s: [UInt8]`, cursor `i`) and builds the tree directly. There is also no55intermediate bytecode: the evaluator interprets the `Expr` tree.5657## Parser5859`FormulaParser.parse(_ text:syntax:)` accepts the text with or without the leading `=` and throws a60`ParseError` (message + UTF-8 offset) on failure. Whitespace (space, tab, CR, LF) is skipped between tokens.6162### Operator precedence6364| Level (low → high) | Operators | Associativity | Where |65|---|---|---|---|66| 1 | `=` `<>` `<` `<=` `>` `>=` | left | `binaryOp()` precedence 1 |67| 2 | `&` (concatenation) | left | precedence 2 |68| 3 | `+` `-` | left | precedence 3 |69| 4 | `*` `/` | left | precedence 4 |70| 5 | `^` | **left** | precedence 5 |71| prefix | unary `-`, unary `+`, `@` (implicit intersection) | right (stackable: `+-+3`) | `parseUnary()` |72| postfix | `%`, `#` (spill reference, only directly after a cell reference), `:` (generic range operator), `(…)` (invoke a lambda-valued expression) | left | `parsePostfix()` |73| primary | literals, references, names, function calls, `( )`, `{ }` | | `parsePrimary()` |7475Source: `Packages/SheetCore/Sources/SheetCore/Formula/Parser.swift` (`binaryOp`, `parseBinary`, `parseUnary`, `parsePostfix`).7677Two consequences of this table reproduce documented Excel quirks and are deliberate (the header comment of78`Parser.swift` states them):7980- **Unary minus binds tighter than `^`.** `parseBinary` first calls `parseUnary`, so the left operand of `^`81 already includes its sign. `=-2^2` → 4, `=0-2^2` → -4, `=-2^-2` → 0.25.82- **`^` is left-associative.** The right operand of every binary operator is parsed with `prec + 1`, so83 `=2^3^2` → 64 (that is, (2^3)^2), not 512.8485Comparison operators chain left to right like the others: `=1=1=1` → FALSE (`TRUE=1` is FALSE because86booleans and numbers never compare equal, see [numerical-precision.md](../mathematics/numerical-precision.md)).8788Percent is a postfix operator and can repeat: `=10%%` → 0.001. `=5 %` parses (whitespace is skipped before a89postfix operator) and prints back as `5%`.9091Nesting is limited to 64 levels (`maxDepth`); deeper formulas fail with "Formula is nested too deeply".92The counter is incremented by every `parseBinary` and unary-operator recursion, so parentheses, function93arguments and prefix signs all count.9495### Literals9697- **Numbers**: digits, an optional decimal part and an optional exponent (`1E3`, `1e+20`, `.5`, `5.`). The98 text is handed to Swift's `Double(String)`; a literal that overflows (`=1E400`) parses to infinity and99 evaluates to `#NUM!`. There are no negative number literals outside array constants: `-3` is unary minus100 applied to 3.101- **Text**: `"…"` with `""` as the escaped quote.102- **Booleans**: the bare words `TRUE` / `FALSE` in any case. `TRUE()` and `FALSE()` are ordinary registered103 functions.104- **Errors**: any `CellError` raw value, matched case-insensitively: `#NULL!`, `#DIV/0!`, `#VALUE!`, `#REF!`,105 `#NAME?`, `#NUM!`, `#N/A`, `#SPILL!`, `#CALC!` and the ORIX extension `#CIRC!`.106- **Array constants**: `{1,2;3,4}` — values only (numbers with an optional sign, text, booleans, errors);107 rows must have the same length; an expression inside braces is a parse error.108109### References110111| Form | Example | AST node |112|---|---|---|113| Cell, with optional `$` markers | `B7`, `$B$7` | `.cell(sheet:, CellAddr)` |114| Cell range | `A1:C10` | `.range(RangeExpr kind .cells)` |115| Whole columns | `A:C`, `$A:$C` | `.range(kind .columns)` — stored as rows 0…1 048 575 |116| Whole rows | `1:3`, `$1:$3` | `.range(kind .rows)` — stored as columns 0…16 383 |117| Sheet-qualified | `Sheet2!A1`, `'My Sheet'!A1:B2` | same nodes with `sheet` set |118| Generic range operator | `A1:INDEX(B:B,3)` | `.binary(.range, lhs, rhs)` — the union rectangle of two references on the same sheet, else `#REF!` |119| Spill reference | `F1#` | `.spill(.cell)` |120| Implicit intersection | `@A1:A10` | `.implicit(…)` |121| Structured reference | `Table1[Qty]`, `Table1[[#Headers],[Qty]:[Price]]`, `[@Qty]` | `.structured(StructuredRef)` |122| Name | `Rate`, `Sheet1!LocalName` | `.name(String, sheet:)` |123124Source: `Packages/SheetCore/Sources/SheetCore/Formula/AST.swift`, `Parser.swift` (`tryReference`, `scanRefPart`, `parseStructured`, `parsePostfix`).125126Details that matter:127128- Coordinates are stored **absolute and zero-based**; the `$` flags only record the markers and drive129 copy/fill adjustments (`CellAddr`).130- The grid limits are those of OOXML: 1 048 576 rows × 16 384 columns (`Limits`). A token that looks like a131 reference but is outside the grid is not a reference: `=XFE1` and `=A1048577` parse as **names** and132 evaluate to `#NAME?`.133- Reversed whole-row / whole-column ranges are normalized at parse time: `=B:A` prints back as `A:B`,134 `=SUM(2:1)` as `SUM(1:2)`.135- A quoted sheet name uses `''` for an embedded apostrophe. The printer quotes a sheet name unless it is a136 plain identifier that could not be mistaken for a cell reference (`FormulaPrinter.quoteSheet`).137- Function names are uppercased and the OOXML prefixes `_xlfn._xlws.`, `_xlfn.`, `_xlws.` and `_xll.` are138 stripped at parse time (`canonicalFunctionName`), so the AST never contains them. Whether they are written139 back on XLSX export is a SheetIO matter (see [../compatibility/excel.md](../compatibility/excel.md)).140- An omitted argument is kept as `.missing` (`PMT(a,b,,d)`, `IF(1,,5)`, also a trailing `SUM(A1:A3,,)`).141142### Not parsed143144These constructs are **not implemented** in the parser; they produce a `ParseError` (and145`CalcEngine.evaluate(text)` then returns `#NAME?`, `setFormula` returns `false`):146147| Construct | Example | Observed |148|---|---|---|149| Intersection operator (space) | `=A1:B2 B2:C3` | parse error "Unexpected 'B'" |150| Union operator (comma inside parentheses) | `=SUM((A1,B2))` | parse error |151| 3-D references | `=Sheet1:Sheet3!A1` | parse error |152| External workbook references | `=[Book1.xlsx]Sheet1!A1` | parse error (the bracket is read as a structured reference) |153| R1C1 notation | `=R1C1` | parsed as a name → `#NAME?`; `INDIRECT(text, FALSE)` → `#REF!` |154| OpenFormula range dot | `=A1.A3` | parse error |155156Source: probe run on 2026-11-21; `Parser.swift` has no code path for these forms and `Builtins.swift` rejects R1C1 in `INDIRECT`.157158Because there is no union operator, `AREAS` always returns 1 and `INDEX` accepts but ignores an `area_num`159argument (see [../compatibility/formulas.md](../compatibility/formulas.md)).160161## AST162163`Expr` is an `indirect enum`, `Sendable` and `Hashable`, with 18 cases: `number`, `text`, `bool`, `error`,164`missing`, `array`, `cell`, `range`, `name`, `structured`, `unary`, `percent`, `binary`, `call`, `invoke`,165`spill`, `implicit`, `paren`. Parentheses are kept as a node so that the printer reproduces what the user166typed. Two helpers exist: `walk` (pre-order visit) and `map` (bottom-up rebuild), plus `functionNames`.167168The tree is immutable and locale independent. A cell holds a `Formula` object (`Model/Workbook.swift`) with169the `expr`, the last computed `value`, the current `spill` area and the scheduling state. Rewriting a formula170(row insertion, fill, paste) always creates a new `Formula` object, which keeps undo snapshots valid.171172## Printer and locale syntax173174`Expr.formulaText(syntax:)` produces the text without the leading `=`. Binary operators are written without175spaces, function names in upper case, numbers that are integers below 1e15 without a decimal point and other176numbers with Swift's shortest round-trip description (`e` replaced by `E`). The original spacing and the case177of function names are not preserved (`=sum( A1 , 2 )` prints as `SUM(A1,2)`); the case of names is.178179`FormulaSyntax` carries four separators. Two presets exist:180181| Preset | Argument separator | Decimal separator | Array column separator | Array row separator | Example |182|---|---|---|---|---|---|183| `.canonical` | `,` | `.` | `,` | `;` | `=SUM(1.5,2)` |184| `.semicolon` | `;` | `,` | `.` | `;` | `=SUM(1,5;2)` |185186Source: `Packages/SheetCore/Sources/SheetCore/Formula/AST.swift` (`FormulaSyntax`).187188`FormulaSyntax.forLocale` selects `.semicolon` when the locale's decimal separator is a comma, `.canonical`189otherwise. The canonical form is the one stored in files and used internally (defined-name formulas,190conditional-format and validation formulas, `INDIRECT` text are always parsed with it). The session parses191what the user types and prints what the formula bar shows with the syntax of its locale192(`WorkbookSession.syntax`), so the same stored formula reads `=SUM(1.5,2)` or `=SUM(1,5;2)`.193194Limits of the locale layer, stated plainly:195196- **Function names are not translated.** `SOMME` is not `SUM`; names are English in every locale.197- Only the separators change. `InputParser` additionally accepts `VRAI` / `FAUX` as typed boolean values, but198 inside a formula only `TRUE` / `FALSE` are booleans.199- The text and date functions are registered once with the en-US format locale200 (`ORIXEngine.bootstrap` calls `TextFunctions.register()` whose default is `locale: .enUS`), independently201 of the interface language.202203### Typed input204205`InputParser.parse` decides what a typed string is, in this order: empty → blank; leading apostrophe → text;206leading `=` (and more than one character) → formula, a syntax error is thrown to the editor; leading `+` or207`-` that is not a number and parses as a formula that is not a plain name → formula; `TRUE`/`VRAI`,208`FALSE`/`FAUX` → boolean; an error literal → error value; a number (grouping, currency symbol, percent,209parentheses, exponent) → number with a suggested number format; a date or time → serial number with a210suggested format; anything else → text. This path is only used for interactive typing and text paste; file211importers have their own rules (see [../compatibility/formats.md](../compatibility/formats.md) and212[../security.md](../security.md) for formula injection from CSV).213214## Evaluator215216`EvalContext` holds the state of one evaluation thread: the workbook, the registry, the sheet and cell of the217formula being evaluated, the `now` timestamp shared by every `NOW()`/`TODAY()` of one pass, the lexical218environment of `LET`/`LAMBDA` (`env`), recursion counters and small caches (sheet-name index, parsed defined219names). It is documented as not thread-safe: one context per thread.220221### Operands222223`evaluate(_:)` returns an `Operand`, not a value:224225| Operand | Meaning |226|---|---|227| `.value(Value)` | a scalar, an array (`ArrayValue`, row-major, never nested) or a lambda |228| `.ref(AreaRef)` | a rectangle on a sheet — **not yet read** |229| `.missing` | an omitted argument |230231Source: `Packages/SheetCore/Sources/SheetCore/Engine/Registry.swift`.232233Keeping references unread until a function asks for them is what makes whole-column arguments cheap and234lets `INDEX`, `OFFSET`, `INDIRECT`, `XLOOKUP`, `IF`, `CHOOSE`, `IFERROR` and `LET` return references. A235reference is turned into a value by one of the context helpers:236237| Helper | Behaviour |238|---|---|239| `deref` | single cell → its value; multi-cell range → array (1×1 collapses to a scalar) |240| `scalar` | top-left value |241| `array` | dense matrix; whole rows/columns are clamped to the used range of the sheet (`effectiveRange`); more than 20 000 000 cells → `#CALC!` (`maxMaterializedCells`) |242| `forEachValue` | visits only the stored, non-blank cells (order unspecified) |243| `forEachNumber` | the aggregate rule of SUM/AVERAGE: in references and arrays only numbers count (text and booleans ignored); direct arguments are coerced (TRUE → 1, `"3"` → 3, other text → `#VALUE!`); errors propagate |244245Source: `Packages/SheetCore/Sources/SheetCore/Engine/Evaluator.swift`.246247Every helper that reads cells first calls the engine hook `ensureClean(area)`, which computes any formula in248that area that is still dirty (see [calculation-graph.md](calculation-graph.md)).249250### Operators, lifting and broadcasting251252Unary minus, percent and all binary operators except `:` dereference their operands and are applied253element-wise:254255- `lift1` maps a unary operation over an array; an error element stays that error.256- `lift2` broadcasts a binary operation. Two arrays produce an array of `max(rows) × max(cols)`; a dimension257 of size 1 is repeated; positions outside a larger-than-1 dimension give `#N/A` (`pick`). An error in either258 operand wins, **left operand first**: `=#N/A+#DIV/0!` → `#N/A`.259- Arithmetic coerces with `Value.num()` (blank → 0, TRUE → 1, numeric text → number, other text →260 `#VALUE!`), concatenation with `Value.str()`, comparisons use `compareValues`. The exact rules are in261 [numerical-precision.md](../mathematics/numerical-precision.md).262- Division by zero is `#DIV/0!`; `0^0` is `#NUM!`; `0^negative` is `#DIV/0!`; any non-finite result is263 `#NUM!` (`Value.num(_:)`).264265A formula whose result is an array of more than one element **spills**; there is no legacy266"implicit intersection unless array-entered" mode and no `{=…}` array-formula entry. `@` is available to267request intersection explicitly: for a one-column range it picks the row of the formula's cell, for a268one-row range its column, otherwise `#VALUE!`; applied to an array value it takes the top-left element.269270### Function calls and argument lifting271272`evalCall` resolves a call in this order:2732741. the eight **special forms** (below), matched by name before anything else;2752. a `LET`/`LAMBDA` variable of that name holding a lambda (`env`);2763. a registered function (`registry.lookup`);2774. a defined name of that name that evaluates to a lambda;2785. otherwise `#NAME?`.279280For a registered function the argument count is checked against `minArgs…maxArgs` (`#VALUE!` when outside —281a wrong argument count is not a parse error), each argument is evaluated with `evaluateArg` (a thrown282`CellError` becomes an error value), then `apply(spec, operands)` runs. `apply` implements **automatic283lifting**, driven by the declared kind of each parameter:284285| `ParamKind` | Treatment before the implementation is called |286|---|---|287| `.scalar` | dereferenced; an error short-circuits the whole call; an array or multi-cell range triggers lifting |288| `.scalarOrError` | like `.scalar` but error values are passed to the function |289| `.any` | passed untouched (reference, array, value or missing) |290291Source: `Packages/SheetCore/Sources/SheetCore/Engine/Registry.swift`, `Evaluator.swift` (`apply`).292293When at least one scalar parameter received an array, the implementation is called once per element of the294broadcast shape and the results are assembled into an array (errors per element). This is why295`=LEN(A1:A3)` returns three lengths and spills, without `LEN` knowing about arrays. `FunctionRegistry.scalar`296registers an all-scalar function in one line; `FunctionRegistry.raw` registers a function that receives the297operands as they are (aggregates, lookups, anything that needs a reference).298299### Special forms300301Eight names are evaluated by the evaluator itself because they need lazy evaluation or bind names. They are302not in the registry (`ORIXEngine.specialForms`).303304| Form | Behaviour implemented |305|---|---|306| `IF(cond, then, [else])` | Scalar condition: only the chosen branch is evaluated, and a reference result stays a reference. Missing `else` → FALSE; an empty placeholder (`IF(1,,5)`) → 0. Array condition: both branches are evaluated and the result is built element-wise (blank elements become 0). |307| `IFERROR(value, alt)` / `IFNA(value, alt)` | `alt` is evaluated only when needed. Array `value`: element-wise replacement. A reference that is not an error is returned as a reference. |308| `IFS(c1, v1, …)` | Pairs evaluated in order, first true condition wins; none → `#N/A`; odd argument count → `#VALUE!`. Conditions are taken as scalars (top-left). |309| `SWITCH(expr, v1, r1, …, [default])` | Compares with `compareValues` (text case-insensitive); an error subject propagates; no match and no default → `#N/A`. |310| `CHOOSE(index, v1, …)` | Scalar index: only the chosen argument is evaluated (references survive). Array index: every option is evaluated and the result is element-wise. Out of range → `#VALUE!`. |311| `LET(name1, value1, …, result)` | Names are bound in order in `env` (later values see earlier names); the environment is restored afterwards. Argument count must be odd and ≥ 3. |312| `LAMBDA(p1, …, body)` | Returns a `LambdaValue` capturing the current environment (a closure). |313314Source: `Packages/SheetCore/Sources/SheetCore/Engine/Evaluator.swift` (`evalCall`); `IF(1,,5)` → 0, `IF(0,1)` → FALSE, `LET(x,1,y,x+1,y*2)` → 4 observed on 2026-11-21.315316`AND`, `OR`, `XOR`, `NOT` are ordinary registered functions: all their arguments are evaluated (no317short-circuit).318319### Lambdas320321- A lambda is a value (`Value.lambda`), compared by identity. Parameter names are stored uppercased.322- It can be invoked immediately (`=LAMBDA(x,x+1)(5)` → 6, the `.invoke` node), through a `LET` name, through323 a defined name (`Twice(4)` with `Twice` defined as `LAMBDA(x,x*2)` → 8), or by the lambda helper functions324 of `FunctionsLookup` (`MAP`, `REDUCE`, `SCAN`, `BYROW`, `BYCOL`, `MAKEARRAY`).325- Passing more arguments than parameters is `#VALUE!`. Passing fewer is allowed: the missing parameters are326 bound to `.missing` (`=LAMBDA(a,b,a+b)(1)` → 1); `ISOMITTED` tests for it.327- Nested lambda calls are limited to 400 (`lambdaDepth`), beyond which the call returns `#NUM!`. Recursion328 through a defined name therefore terminates with `#NUM!` rather than a crash.329- A formula whose final result is a lambda displays `#CALC!` (`CalcEngine.store`). `TYPE` of a lambda is 128.330331## Function registry332333`FunctionRegistry` is a lock-protected dictionary from upper-case name to `FunctionSpec`. A spec carries the334name, the argument-count bounds (`variadic` = 255), the parameter kinds, the volatility flag, a335`FunctionCategory`, a signature string (for example `PMT(rate, nper, pv, [fv], [type])`), a one-line summary336and the implementation closure `(EvalContext, [Operand]) throws -> Operand`.337338`FunctionRegistry.standard` is created with the 24 core built-ins that the engine itself needs (aggregates,339logical functions, reference functions, `NOW`/`TODAY`/`RAND`). `ORIXEngine.bootstrap()` — to be called before340the first `CalcEngine` is created — registers the libraries once per process:341342| Library | Entry point | Functions registered |343|---|---|---:|344| `SheetCore` | `CoreBuiltins.register(into:)` | 24 |345| `FunctionsNumeric` | `NumericFunctions.register(into:)` | 296 |346| `FunctionsText` | `TextFunctions.register(into:locale:)` | 64 |347| `FunctionsLookup` | `LookupFunctions.register(into:)` + `FinancialFunctions.register(into:)` | 81 |348| **Registry total** | | **465** |349| Special forms (evaluator) | `ORIXEngine.specialForms` | 8 |350| **`ORIXEngine.functionCount`** | | **473** |351352Source: `docs/compatibility/function-registry.json`, produced on 2026-11-21 by registering each library into a fresh `FunctionRegistry`; no name is registered by two libraries.353354Registering a name twice replaces the earlier spec (dictionary assignment), so registration is idempotent.355Signatures and summaries are the data behind function help in the UI and behind the generated catalogue in356[../compatibility/formulas.md](../compatibility/formulas.md), which also gives the counts per category.357358**Extension point.** A package adds worksheet functions by calling `FunctionRegistry.standard.scalar(…)` or359`.raw(…)` at start-up; nothing else in the engine has to change. Volatility is declared with360`volatile: true`. A function that depends on something the dependency graph cannot see (row visibility, the361clock, external state) must either be volatile or be handled like `SUBTOTAL`/`AGGREGATE` (see362[calculation-graph.md](calculation-graph.md)).363364Errors inside an implementation are raised by throwing a `CellError`; the evaluator converts it into an365error value at the argument or cell boundary. Any other Swift error inside a lifted element becomes `#VALUE!`.366367## Structured references368369A `StructuredRef` stores the optional table name, the item keywords (`#ALL`, `#DATA`, `#HEADERS`,370`#TOTALS`, uppercased), the this-row flag (`@` or `[#This Row]`) and an optional first and last column name.371`'` escapes special characters inside a column name. `EvalContext.resolveStructured` turns it into an372`AreaRef`:373374| Reference | Rows selected | Failure |375|---|---|---|376| `Table1[Qty]`, `Table1[[#Data],[Qty]]`, `Table1` (as a name) | data rows | `#REF!` when the table has no data rows |377| `Table1[#All]` | header, data and totals rows | |378| `Table1[#Headers]` | header row | `#REF!` when the table has no header |379| `Table1[#Totals]` | totals row | `#REF!` when the table has no totals row |380| several items (`[[#Headers],[#Data]]`) | the covering row span | |381| `[@Qty]`, `Table1[@Qty]`, `Table1[[#This Row],[Qty]]` | the row of the formula's cell | `#VALUE!` when the cell is on another sheet or outside the data rows |382| `[Qty]:[Price]` | columns from the first to the last named column | `#REF!` for an unknown column |383| no table name | the table that contains the formula's cell | `#REF!` when the cell is in no table |384385Source: `Packages/SheetCore/Sources/SheetCore/Engine/Evaluator.swift` (`resolveStructured`).386387Table names are matched case-insensitively across all sheets (`Workbook.table(named:)`). A structured388reference is registered in the dependency graph as the rectangle it resolves to **at registration time**389(`collectPrecedents`); the registration is refreshed whenever the graph is rebuilt (every structural edit).390`RefTransform` rewrites cell references, ranges and sheet-qualified names; structured-reference nodes are391left untouched because they follow the table definition, which is transformed with the sheet metadata.392393## Defined names394395`DefinedName` holds a name, a formula text **without `=` in canonical syntax** (for example396`Sheet1!$A$1:$B$5`, `0.05` or `LAMBDA(x,x*2)`), an optional sheet scope, a comment and a hidden flag. Lookup397is case-insensitive; a sheet-scoped name shadows the workbook-scoped one for formulas on that sheet398(`Workbook.definedName(_:sheetID:)`).399400Resolution of a bare name (`resolveName`), in order:4014021. a `LET`/`LAMBDA` binding of that name (only when the name is not sheet-qualified);4032. a defined name visible from the sheet — its formula is parsed (the parse is cached per context) and404 evaluated in the context of the calling cell. Because AST coordinates are absolute, a reference without405 `$` inside a name still designates a fixed cell: names relative to the calling cell are not supported;4063. a table of that name → its data range;4074. otherwise `#NAME?`.408409Names may refer to other names up to 64 levels; deeper (or circular) chains give `#CIRC!`. When precedents410are collected for the dependency graph, names are expanded up to 16 levels.411412Defining or deleting a name is a structural edit (`CalcEngine.setDefinedName` → `structural`), which413rebuilds the whole dependency graph; see [calculation-graph.md](calculation-graph.md) for the cost.414415## Reference rewriting416417Formulas are never edited as text. Structural operations build a `RefTransform` (`insertRows`, `deleteRows`,418`insertCols`, `deleteCols`, `renameSheet`, `move`, `shift`) and map it over the AST of each formula, creating419new `Formula` objects. A reference whose target is deleted becomes a `#REF!` literal in the tree; a range420that partly survives is shrunk (`RefTransform.span`). `shift` moves only the relative parts of references and421is what copy, paste and fill use. The same transform is applied to sheet metadata (merges, tables,422conditional-format ranges, row and column sizes) and offered to feature packages through423`CalcEngine.structuralObservers` so that they can remap references they store in their own payloads.424425## Errors426427| Error | Raised by the engine when |428|---|---|429| `#NULL!` | never raised by the core (no intersection operator); can be typed or imported |430| `#DIV/0!` | division by zero, `0^negative`, `AVERAGE` without numbers |431| `#VALUE!` | text that is not a number in arithmetic, wrong argument count, bad implicit intersection, too many lambda arguments |432| `#REF!` | unknown sheet or table, invalid structured reference, `:` between different sheets, deleted reference, out-of-grid `OFFSET` |433| `#NAME?` | unknown function or name; `CalcEngine.evaluate(text)` on text that does not parse |434| `#NUM!` | non-finite result, `0^0`, lambda recursion deeper than 400 |435| `#N/A` | `NA()`, `IFS`/`SWITCH` without match, broadcasting past the end of an array |436| `#SPILL!` | spill area blocked or outside the grid |437| `#CALC!` | a lambda as cell result, materializing more than 20 000 000 cells |438| `#CIRC!` | circular reference (ORIX extension; Excel shows a warning and 0) |439440Source: `Packages/SheetCore/Sources/SheetCore/Model/Value.swift`, `Engine/Evaluator.swift`, `Engine/CalcEngine.swift`, `Engine/Builtins.swift`.441442`ERROR.TYPE` returns Excel's codes 1–7, and 9 for `#SPILL!`, 14 for `#CALC!`, 15 for `#CIRC!`443(`CellError.typeCode`).444445## What is not implemented446447- Intersection and union operators, 3-D references, external workbook references, R1C1 notation (see448 "Not parsed").449- Legacy array formulas (`{=…}` entered with Ctrl+Shift+Enter) as a distinct concept: every array result450 spills.451- Translated function names and locale-specific list separators beyond the two presets.452- A compiled or vectorized evaluation path: the tree is interpreted per cell.453- A cross-cell cache for repeated lookups (stated in `Packages/FunctionsLookup/COMPATIBILITY.md`).454- Stale comment to be aware of: `Builtins.swift` says the other functions live "in the SheetFunctions455 library"; no package of that name exists — they are in `FunctionsNumeric`, `FunctionsText` and456 `FunctionsLookup`.457