runloopable / web /src /sheet /formulaEval.ts
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
8133df9 verified
Raw History Blame Contribute Delete
168 kB
// sheet/formulaEval.ts β€” the CLIENT half of contract C1's calc split (wave 53, W53-T01). // // ⭐⭐ RULING R2: a formula that references only OTHER CELLS evaluates HERE, the instant it is // committed, and never touches the network. `evaluateFormula` is the whole of that: a small, // self-contained engine over literals, A1 cell references (with Excel's `$` tolerated), // the four operators, parentheses, and C1's five aggregate functions (SUM/COUNT/AVG/MIN/MAX) plus // COUNTA (W54-T27), each run over one simple range. It is NOT a reimplementation of the server's // full grammar (`api/formula_runtime.py::evaluate_sheet`). W57-T06 adds scalar lookup results // and comma-separated lookup arguments. Cross-sheet references are admitted only through // an explicitly workbook-aware getter. W57-T07 adds five server-backed text/date functions // through the same argument parser; unsupported forms still belong to Refresh. // // β›”β›” AND WHAT IT CANNOT ANSWER IS `unsupported`, NEVER AN ERROR. That distinction is the whole // safety of shipping a second, smaller engine beside the server's: `=IF(...)` and a qualified // reference handed to an ordinary sheet-local getter are perfectly good formulas this file simply // does not speak, so they fall back to EXACTLY the behaviour that existed before this ticket β€” the // cell stays `stale`, keeps its last computed value, and the server's Refresh resolves it. A caller // must opt into qualified reads with `workbookFormulaGetter`; accepting one against an ordinary // getter would silently read the same-named LOCAL cell, which is worse than the round trip. // // β›”β›” THIS FILE IMPORTS NOTHING FROM THE PROJECT, ON PURPOSE. It is compiled and driven standalone // by `verify_formula_engine.py`, the same convention `a1.ts` already uses. A bare module with no // project imports can never be the reason an unrelated compile's emitted root moves // ([[tsc-outdir-root-moves-with-the-import-graph]], the lesson `sheetState.ts`'s own header // carries). `sheetState.ts::cellToValue` is the ONE place a `Cell` β€” a type this file has never // heard of β€” becomes the `CellValue` this file's `get` callback returns. /** * What one referenced cell answers with, and what `evaluateFormula` returns. * * ⭐⭐ `pending` COVERS TWO DIFFERENT FACTS, AND BOTH ARE CARRIED. A referenced cell with NO value * at all yet (a bound cell never resolved) is `{ kind: "pending" }` β€” there is nothing to compute * with, and it is treated as zero for arithmetic so a formula never blocks. A referenced cell that * DOES have a last known value, but is itself waiting on a fresh server read, is a `number` or * `text` value with `pending: true` riding along: the value is used for arithmetic immediately, * and the flag rides up through every operation so the FORMULA's own result can say "this used a * value that is not fresh" and recompute once the input resolves. * * ⚠ `unsupported` IS PRODUCED BY THIS FILE AND NEVER BY A GETTER. It means "the server owns this * one", not "this is wrong"; see the header. */ export interface SheetArray { values: CellValue[]; rows: number; cols: number } export type CellValue = ( | { kind: "number"; value: number; pending?: boolean } | { kind: "text"; value: string; pending?: boolean } | { kind: "boolean"; value: boolean; pending?: boolean } | { kind: "empty" } | { kind: "pending" } | { kind: "unsupported"; reason: string } | { kind: "error"; message: string; code?: FormulaErrorCode; origin?: string; via?: string | null; path?: string[] }) & { array?: SheetArray; style?: Record<string, unknown>; formulaMeta?: Record<string, unknown> }; /** * Internal aggregate keys. AVERAGE and the legacy AVG spelling share the same evaluator. * These are not the formula catalogue or the vocabulary of a bound database rollup. */ export const AGGREGATE_FUNCTIONS = ["SUM", "COUNT", "AVG", "MIN", "MAX"] as const; export type AggregateFunction = (typeof AGGREGATE_FUNCTIONS)[number]; /** * ⭐⭐ W54-T27 / RULING R9(iii): `COUNTA` IS THE SIXTH RANGE FUNCTION, AND IT IS DELIBERATELY NOT * A MEMBER OF THE LIST ABOVE. {@link AGGREGATE_FUNCTIONS} names internal dispatch keys. * The bound rollup vocabulary is separately guarded in api_sheet; formula names do not define it. * * ⭐ IT IS SAFE TO TYPE BECAUSE THE SERVER ALREADY SPEAKS IT. `api/formula_runtime.py` carries * `COUNTA` in both `FN_ARITY` and `FN_CATALOGUE`, and `functionCatalog.ts` already offers it in * the suggestion list, so a `COUNTA` a person types survives the save and comes back with the * same answer this file computes. This is the one place the two engines had to agree first. */ export const COUNTA_FUNCTION = "COUNTA"; /** Every function name this engine reads ahead of one simple range: the five, plus `COUNTA`. */ export const RANGE_FUNCTIONS = [...AGGREGATE_FUNCTIONS, COUNTA_FUNCTION] as const; const AGGREGATE_SET: ReadonlySet<string> = new Set<string>(RANGE_FUNCTIONS); /** * ⭐⭐ THE ONE SENTENCE A CYCLE STATES, DECLARED HERE SO ONE FILE OWNS IT. `sheetState.ts` * imports this rather than spelling it, because a refusal message a reader sees in a cell and a * gate asserts by text must have exactly one source. Sentence case, and no dash of either width * (standing rule 2): every `FormulaError` message in this file reaches a cell. */ export const CYCLE_REFUSAL = "This formula refers back to itself."; // ── W54-T26 / RULING R9(ii): AN ERROR IS A VALUE, AND A VALUE HAS A CODE ────────────────────── // // ⭐⭐ WHAT WAS WRONG, MEASURED IN A BROWSER BEFORE THIS TICKET. `=1/0` painted the ENGLISH // SENTENCE "This formula divid..." across the cell, clipped by the column, and `=FOO(1)` painted // its own SOURCE TEXT back at the reader. Two different wrongs with one cause: an error had no // VALUE to be, so a cell had nothing to show but prose or the formula it could not compute. // // β›” DESIGN.md 4, "refusals: never over-explain". A cell states a CODE. The sentence is still // carried, first token after the code, so a disclosure can state the diagnosis without the grid // having to; nothing about that sentence reaches the cell face. // // ⚠ THE CODES ARE EXCEL'S OWN TOKENS AND ARE SPELLED EXACTLY. They are not ours to prettify, // translate or shorten: a reader who knows spreadsheets already knows what each one means, and a // near miss (`#DIV0!`, `#NAME!`) reads as a bug in us rather than a fault in their formula. export const ERROR_DIV0 = "#DIV/0!"; export const ERROR_REF = "#REF!"; export const ERROR_NAME = "#NAME?"; export const ERROR_VALUE = "#VALUE!"; /** * β›” THE CLOSED VOCABULARY. `types.ts` declares the SAME four for the renderer, and the gate * family asserts one set across the two files, exactly as {@link AGGREGATE_FUNCTIONS} is asserted * against the server's own list. This file imports nothing from the project (see the header), so * a second declaration is the price of that rule and a parity leg is what keeps the price honest. */ export const FORMULA_ERROR_CODES = [ERROR_DIV0, ERROR_REF, ERROR_NAME, ERROR_VALUE] as const; export const REFERENCE_ERROR_CODES = ["#NULL!"] as const; export const ARRAY_ERROR_CODES = ["#SPILL!", "#CALC!", "#N/A", "#NUM!"] as const; export type FormulaErrorCode = (typeof FORMULA_ERROR_CODES)[number] | (typeof REFERENCE_ERROR_CODES)[number] | (typeof ARRAY_ERROR_CODES)[number]; /** * ⭐⭐ THE SENTENCE IS THE IDENTITY OF THE WRONG; THE CODE IS HOW IT READS. This table is the one * place the two are paired, so a sentence cannot reach a cell without a code and a code cannot be * invented at a throw site. * * β›” IT IS KEYED ON THE MESSAGE RATHER THAN PASSED AT THE THROW, AND THAT IS DELIBERATE. Every * `throw new FormulaError(...)` statement in this file is a NEEDLE in `verify_formula_engine.py`'s * compiled-artifact mutation table, which requires each to occur exactly once and drives a * negative control by replacing it whole. Adding an argument to those statements would leave that * gate's targets unfindable and red a wave-53 leg that has nothing to do with error values. */ const ERROR_CODE_BY_MESSAGE: Readonly<Record<string, FormulaErrorCode>> = { "This formula divides by zero.": ERROR_DIV0, "This formula expects a number here.": ERROR_VALUE, "This formula uses a name nothing here answers to.": ERROR_NAME, }; /** β›” Said once, so the throw site and the table above cannot drift apart. */ const NAME_NOT_FOUND = "This formula uses a name nothing here answers to."; /** A code sitting at the head of a message, matched loosely so a near miss is NOT accepted. */ const LEADING_ERROR_CODE = /^(#[A-Z0-9/]{2,6}[!?]?)(?:\s|$)/; /** * ⭐⭐ THE CODE A MESSAGE ALREADY STATES, WHICH IS THE WHOLE OF PROPAGATION. Excel passes an error * through every formula that reads it unchanged: `=A1+1` over a `#DIV/0!` is `#DIV/0!`, not a new * complaint about A1. A referenced cell arrives here as its stored message, so reading the code * back off the front of it is what makes a dependent formula answer with the ORIGINAL fault. */ export const errorCodeOf = (message: string): FormulaErrorCode | null => { const found = LEADING_ERROR_CODE.exec(String(message ?? "").trim()); const code = found ? found[1] : ""; return ([...FORMULA_ERROR_CODES, ...REFERENCE_ERROR_CODES, ...ARRAY_ERROR_CODES] as readonly string[]).includes(code) ? (code as FormulaErrorCode) : null; }; /** * One stated wrong, as a VALUE. A message that already carries a code keeps it (propagation); a * message this file knows gains one; anything else stays a bare message rather than being forced * into a code that would name the wrong fault. */ const errorValue = (message: string): CellValue => { const said = String(message ?? "").trim(); const carried = errorCodeOf(said); if (carried) return { kind: "error", code: carried, message: said }; const code = ERROR_CODE_BY_MESSAGE[said]; if (!code) return { kind: "error", message: said }; return { kind: "error", code, message: code + " " + said }; }; /** A stated wrong: the reader sees this error's CODE in the cell instead of a number. */ class FormulaError extends Error { readonly source?: Extract<CellValue, { kind: "error" }>; constructor(message: string, source?: Extract<CellValue, { kind: "error" }>) { super(message); this.source = source; } } /** Not wrong, just not ours: the cell keeps its old behaviour and the server answers it. */ class Unsupported extends Error {} /** An input has no value yet, so there is no honest number to state at all. */ class PendingInput extends Error {} class PendingReference extends Error {} const CELL_REF = /^[A-Za-z]{1,3}[0-9]{1,7}$/; /** `a1.ts::REF_SOURCE`'s sheet-qualified half, kept local so this module stays Node-drivable. */ const QUALIFIED_REF_AT = /^(?:'((?:[^']|''){1,128})'|([A-Za-z_][A-Za-z0-9_]{0,63}))!(\$?)([A-Za-z]{1,3})(\$?)([1-9][0-9]{0,6})/; interface QualifiedRefToken { key: string; end: number; } /** * Read one qualified reference at an exact offset. * * β›” THE RESULT KEEPS THE SHEET NAME. Dropping it and returning only `A1` would make * `Sheet2!A1` read this tab's A1, a plausible wrong number that the local-only contract below * deliberately prevents. */ const qualifiedRefAt = (text: string, at: number): QualifiedRefToken | null => { const found = QUALIFIED_REF_AT.exec(text.slice(at)); if (!found) return null; const end = at + found[0].length; const after = end < text.length ? text[end] : ""; if (/[A-Za-z0-9_.]/.test(after)) return null; const sheet = (found[1] || found[2] || "").replace(/''/g, "'").toUpperCase(); const addr = (found[4] + found[6]).toUpperCase(); return { key: `${sheet}!${addr}`, end }; }; // ── tokenizer ──────────────────────────────────────────────────────────────────────────────── type Token = | { t: "num"; v: number } | { t: "ref"; v: string } | { t: "fn"; v: string } | { t: "str"; v: string } | { t: "err"; v: FormulaErrorCode } | { t: "op"; v: string }; const OPS = "+-*/():,"; /** * ⚠ A LETTER RUN IS A CELL REFERENCE ONLY WHEN DIGITS FOLLOW IT WITH NO GAP. `SUM` ahead of `(` * is a function name; `A1` is a reference. There is no ambiguity to resolve, because a bare * function name never carries digits, so the two never compete for the same token. * * ⚠ AND A `$` IS SWALLOWED RATHER THAN REFUSED. `=$F$6+G6` is the same arithmetic as `=F6+G6`: * anchoring is a FILL rule (`a1.ts` owns it), and it has no meaning at all to an evaluator. */ const tokenize = (src: string, qualified = false): Token[] => { const text = String(src ?? "").trimStart().replace(/^=/, ""); const tokens: Token[] = []; let i = 0; while (i < text.length) { const ch = text[i]; if (/\s/.test(ch)) { let end = i + 1; while (/\s/.test(text[end] || "") && end < text.length) end += 1; const prev = tokens[tokens.length - 1], next = text.slice(end); if (qualified && prev && (prev.t === "ref" || prev.t === "fn" || prev.v === ")") && /^(?:[A-Za-z_$'\\]|\()/.test(next) && !(prev.t === "fn" && next.startsWith("("))) { tokens.push({ t: "op", v: " " }); } i = end; continue; } if (qualified) { const external = /^(?:'[^']*\[[^']*'|\[[^\]]*\][A-Za-z0-9_ ]*)![^ ,()+*/]+/.exec(text.slice(i)); if (external) { tokens.push({ t: "err", v: ERROR_REF }); i += external[0].length; continue; } const name = /^(?:('(?:[^']|'')+'|[A-Za-z_][A-Za-z0-9_.]*)!)?([A-Za-z_\\][A-Za-z0-9_.\\]*)/.exec(text.slice(i)); if (name && !/[$!]/.test(text[i + name[0].length] || "") && (!CELL_REF.test(name[2]) || /^\s*\(/.test(text.slice(i + name[0].length)))) { const qualifier = name[1] ? name[1].replace(/^'|'$/g, "").replace(/''/g, "'") + "!" : ""; tokens.push({ t: "fn", v: (qualifier + name[2]).toUpperCase() }); i += name[0].length; continue; } const operator = /^(<=|>=|<>|!=|[=<>^&])/.exec(text.slice(i)); if (operator) { tokens.push({ t: "op", v: operator[0] }); i += operator[0].length; continue; } } if (ch === "#" && !/[A-Za-z]/.test(text[i+1] || "")) { tokens.push({ t: "op", v: "#" }); i += 1; continue; } if (OPS.includes(ch)) { tokens.push({ t: "op", v: ch as "+" | "-" | "*" | "/" | "(" | ")" | ":" | "," }); i += 1; continue; } const workbookRef = qualifiedRefAt(text, i); if (workbookRef) { // β›” QUALIFIED SYNTAX NEEDS A QUALIFIED GETTER. Letting an ordinary `get("A1")` answer // here is the exact same-named-local-cell miswiring this ticket forbids. if (!qualified) throw new Unsupported("qualified reference"); tokens.push({ t: "ref", v: workbookRef.key }); i = workbookRef.end; continue; } if (/[0-9.]/.test(ch)) { let j = i; while (j < text.length && /[0-9.]/.test(text[j])) j += 1; const n = Number(text.slice(i, j)); if (!Number.isFinite(n)) throw new Unsupported("number"); tokens.push({ t: "num", v: n }); i = j; continue; } if (/[A-Za-z$]/.test(ch)) { let j = i; if (text[j] === "$") j += 1; const lettersFrom = j; while (j < text.length && /[A-Za-z]/.test(text[j])) j += 1; const letters = text.slice(lettersFrom, j); let k = j; if (text[k] === "$") k += 1; const digitsFrom = k; while (k < text.length && /[0-9]/.test(text[k])) k += 1; const word = letters + text.slice(digitsFrom, k); if (k > digitsFrom && CELL_REF.test(word)) { tokens.push({ t: "ref", v: word.toUpperCase() }); i = k; continue; } if (!letters) throw new Unsupported("token"); tokens.push({ t: "fn", v: letters.toUpperCase() }); i = j; continue; } // ⭐⭐ W54-T26: AN ERROR LITERAL IS A TOKEN, because Excel writes one INTO a formula. A // structural delete that invalidates a reference leaves `#REF!` in the text, and a workbook // imported from Excel carries whatever codes it was saved with, so an engine that cannot read // one back answers about a formula nobody typed. if (ch === "#") { const literal = [...FORMULA_ERROR_CODES, ...REFERENCE_ERROR_CODES, ...ARRAY_ERROR_CODES].find((code) => text.startsWith(code, i)); if (!literal) throw new Unsupported("token"); tokens.push({ t: "err", v: literal }); i += literal.length; continue; } // ⭐ W54-T26: A QUOTED RUN IS A TEXT LITERAL, so `=1+"x"` can say `#VALUE!` about the thing // that is actually wrong with it. W57-T06 also preserves literals as lookup arguments; // concatenation and comparisons still belong to the server. if (ch === '"' || ch === "'") { if (qualified && ch === '"') { const quoted = /^"(?:[^"]|"")*"/.exec(text.slice(i)); if (!quoted) throw new Unsupported("token"); tokens.push({ t: "str", v: quoted[0].slice(1, -1).replace(/""/g, '"') }); i += quoted[0].length; continue; } const close = text.indexOf(ch, i + 1); if (close < 0) throw new Unsupported("token"); tokens.push({ t: "str", v: text.slice(i + 1, close) }); i = close + 1; continue; } // An unrecognised `!`, a `%`, a `^`: every one of them is a formula the SERVER can read // and this file cannot. See the header: that is `unsupported`, never an error. throw new Unsupported("token"); } return tokens; }; // ── the reference grammar (self-contained: this file imports nothing, see the header) ───────── const colToIndex = (letters: string): number => { let n = 0; for (const ch of letters.toUpperCase()) n = n * 26 + (ch.charCodeAt(0) - 64); return n; }; const colLetters = (n: number): string => { let out = ""; let v = n; while (v > 0) { const rem = (v - 1) % 26; out = String.fromCharCode(65 + rem) + out; v = Math.floor((v - 1) / 26); } return out; }; const splitRef = (ref: string): { sheet: string; col: number; row: number } => { const bang = String(ref ?? "").lastIndexOf("!"); const sheet = bang < 0 ? "" : ref.slice(0, bang).toUpperCase(); const addr = bang < 0 ? ref : ref.slice(bang + 1); const m = /^([A-Za-z]{1,3})([0-9]{1,7})$/.exec(addr); if (!m) throw new Unsupported("reference"); return { sheet, col: colToIndex(m[1]), row: Number(m[2]) }; }; /** One simple rectangle's addresses, row major; a qualified endpoint lends its tab to the other. */ const rangeAddresses = (from: string, to: string): string[] => { const a = splitRef(from); const b = splitRef(to); if (a.sheet && b.sheet && a.sheet !== b.sheet) throw new Unsupported("cross-sheet range"); const sheet = a.sheet || b.sheet; const out: string[] = []; for (let r = Math.min(a.row, b.row); r <= Math.max(a.row, b.row); r += 1) { for (let c = Math.min(a.col, b.col); c <= Math.max(a.col, b.col); c += 1) { const addr = colLetters(c) + String(r); out.push(sheet ? `${sheet}!${addr}` : addr); } } return out; }; // ── the dependency graph: what a formula READS, and what must be recomputed after a write ───── /** Double and single quoted runs blanked out, so `"B4 is the total"` names no cell. */ const withoutQuotes = (src: string): string => String(src ?? "").replace(/"[^"]*"/g, (m) => " ".repeat(m.length)) .replace(/'[^']*'/g, (m) => " ".repeat(m.length)); /** * ⭐⭐ EVERY A1 ADDRESS A FORMULA READS, RANGES EXPANDED, UPPERCASED, `$` TOLERATED. This is the * INPUT EDGE of the recalculation graph and it is deliberately a little generous: over-reporting * a dependency only recomputes a cell that did not need it, while under-reporting leaves a stale * number on screen, which is the exact defect this ticket exists to end. * * β›” A FUNCTION NAME IS NOT AN ADDRESS AND NEITHER IS A SHEET PREFIX. `LOG10(B4)` reads B4 alone * (`LOG10` is rejected by the `(` that follows it) and `Sheet1!B4` reads nothing at all here, * because a cross-sheet reference is the server's to resolve and naming a LOCAL `B4` for it would * recompute the wrong cell. Both are pinned by `verify_formula_engine.py`. */ const scanReferencedAddresses = (text: string): string[] => { const hits = formulaRefHits(text).filter((hit) => !hit.sheet); const out: string[] = []; const seen = new Set<string>(); const push = (addr: string) => { if (seen.has(addr)) return; seen.add(addr); out.push(addr); }; for (let i = 0; i < hits.length; i += 1) { const next = hits[i + 1]; const between = next ? text.slice(hits[i].end, next.start).trim() : ""; if (next && between === ":") { for (const addr of rangeAddresses(hits[i].addr, next.addr)) push(addr); i += 1; continue; } push(hits[i].addr); } return out; }; // 12,287 entries retain the measured 10,000-formula sheet plus roughly 20% edit churn without // letting distinct user text live forever. FIFO eviction keeps cache-hit work constant; the full // source text is the key, so an edited formula cannot reuse a stale scan. const REFERENCE_SCAN_CACHE_LIMIT = 12_287; const referenceScanCache = new Map<string, readonly string[]>(); export const referencedAddresses = (src: string): string[] => { const text = String(src ?? ""); const cached = referenceScanCache.get(text); if (cached) return [...cached]; const refs = scanReferencedAddresses(text); referenceScanCache.set(text, Object.freeze([...refs])); if (referenceScanCache.size > REFERENCE_SCAN_CACHE_LIMIT) { const oldest = referenceScanCache.keys().next().value; if (oldest !== undefined) referenceScanCache.delete(oldest); } return refs; }; interface FormulaRefHit { addr: string; sheet: string; start: number; end: number; } const LOCAL_REF_AT = /^(\$?)([A-Za-z]{1,3})(\$?)([1-9][0-9]{0,6})/; /** Every real reference occurrence, including a quoted sheet name, without reading string text. */ const formulaRefHits = (src: string): FormulaRefHit[] => { const text = String(src ?? "").replace(/^\s*=/, (m) => " ".repeat(m.length)); const out: FormulaRefHit[] = []; let i = 0; while (i < text.length) { const ch = text[i]; // A single quote is a string unless the complete run is a quoted sheet prefix. Double quotes // are always string text in this grammar. Skipping the whole run keeps `"Sheet2!A1"` from // becoming a dependency merely because it looks like one. const qualified = qualifiedRefAt(text, i); if ((ch === "\"" || ch === "'") && !qualified) { const close = text.indexOf(ch, i + 1); i = close < 0 ? text.length : close + 1; continue; } const before = i > 0 ? text[i - 1] : ""; if (qualified && !/[A-Za-z0-9_$!'.]/.test(before)) { const bang = qualified.key.lastIndexOf("!"); out.push({ sheet: qualified.key.slice(0, bang), addr: qualified.key.slice(bang + 1), start: i, end: qualified.end, }); i = qualified.end; continue; } const local = LOCAL_REF_AT.exec(text.slice(i)); if (local) { const end = i + local[0].length; const after = end < text.length ? text[end] : ""; const namePart = /[A-Za-z0-9_$!'.]/.test(before); const callOrName = after === "(" || /[A-Za-z0-9_.]/.test(after); if (!namePart && !callOrName) { out.push({ addr: (local[2] + local[4]).toUpperCase(), sheet: "", start: i, end }); i = end; continue; } } i += 1; } return out; }; const normalizedSheet = (name: string): string => { const text = String(name ?? "").trim(); return (text.startsWith("'") && text.endsWith("'") ? text.slice(1, -1) : text).toUpperCase(); }; /** * The client's workbook key, byte-compatible with `formula_runtime.py::a1_key`. * * A cell on `currentSheet` is bare (`A1`); a cell on any other tab keeps its upper-cased sheet * prefix (`SHEET2!A1`). The sheet name is never discarded, because a bare fallback would silently * select the same address on the wrong tab. */ export const formulaReferenceKey = (sheet: string, addr: string, currentSheet = ""): string => { const name = normalizedSheet(sheet); const current = normalizedSheet(currentSheet); const cell = String(addr ?? "").replace(/\$/g, "").toUpperCase(); return !name || name === current ? cell : `${name}!${cell}`; }; /** * Workbook dependencies, kept separate from {@link referencedAddresses}'s sheet-local contract. * * `ownSheet` says what an unqualified `A1` is local to; `currentSheet` says which tab gets bare * keys in the caller's index. A range inherits a qualified left endpoint, so * `Sheet2!A1:B2` never leaks a bare local `B2` into the graph. */ export const qualifiedReferencedAddresses = ( src: string, ownSheet = "", currentSheet = ownSheet, ): string[] => { const hits = formulaRefHits(src); const out: string[] = []; const seen = new Set<string>(); const push = (sheet: string, addr: string) => { const key = formulaReferenceKey(sheet, addr, currentSheet); if (seen.has(key)) return; seen.add(key); out.push(key); }; for (let i = 0; i < hits.length; i += 1) { const hit = hits[i]; const next = hits[i + 1]; const between = next ? String(src ?? "").slice(hit.end, next.start).trim() : ""; const fromSheet = hit.sheet || ownSheet; const toSheet = next ? (next.sheet || hit.sheet || ownSheet) : ""; if (next && between === ":" && normalizedSheet(fromSheet) === normalizedSheet(toSheet)) { for (const addr of rangeAddresses(hit.addr, next.addr)) push(fromSheet, addr); i += 1; continue; } push(fromSheet, hit.addr); } return out; }; // ── W54-T25 / RULING R9(i): the two pure string rules formula ENTRY needs ───────────────────── // // ⭐⭐ THEY LIVE HERE BECAUSE THIS FILE IS PURE AND DRIVEN UNDER NODE. Both are string // arithmetic with no React, no DOM and no project import, so `verify_formula_engine.py` can // compile this one module and EXECUTE the claims rather than grep for them. `SheetGrid.tsx` is // the only caller: it closes the draft at COMMIT and cycles the anchors on F4. /** * ⭐⭐ AUTO-CLOSE ON COMMIT: `=SUM(A1:A2` becomes `=SUM(A1:A2)`, and it is the STORED string that * changes, never a display of it. `SheetGrid.tsx::commitFormulaEditor` runs the draft through * this before `commitTyped`, so the stored formula, the formula bar's readback and the evaluator * all read one identical closed text. Auto-closing only what is painted would leave a cell that * LOOKS balanced and is stored broken, which is worse than never closing it at all. * * β›” IT ONLY EVER APPENDS. An extra `)` the person actually typed (`=A1)`) is their text and is * left exactly as it is: this rule finishes an unfinished formula, it does not edit a wrong one. * * β›” A QUOTED RUN IS NOT COUNTED, and an UNTERMINATED one refuses the whole repair. `="a("` has * no open bracket, and `=SUM("a` is a string somebody is still in the middle of typing, where a * closing bracket would land inside their label. * * ⚠ NOT A FORMULA, NOT OUR BUSINESS. Text that does not start with `=` comes back untouched, so * a literal cell reading `Total (net` keeps the shape its author typed. */ export const autoCloseFormula = (src: string): string => { const text = String(src ?? ""); if (!text.startsWith("=")) return text; let depth = 0; let quote: string | null = null; for (let i = 0; i < text.length; i += 1) { const ch = text[i]; if (quote !== null) { if (ch === quote) quote = null; continue; } if (ch === '"' || ch === "'") { quote = ch; continue; } if (ch === "(") depth += 1; else if (ch === ")") depth = depth > 0 ? depth - 1 : 0; } if (quote !== null) return text; if (depth <= 0) return text; return text + ")".repeat(depth); }; /** * One reference OCCURRENCE with its offsets and its two dollar flags, so a caller can rewrite * exactly that occurrence and leave every other character of the formula alone. */ interface RefSpan { start: number; end: number; colAbs: boolean; rowAbs: boolean; col: string; row: string; } const REF_SPAN_SCAN = /(\$?)([A-Za-z]{1,3})(\$?)([0-9]{1,7})/g; /** * Every A1 occurrence in `src` WITH ITS OFFSETS, quoted runs blanked and the same two boundary * guards `referencedAddresses` uses: a run inside a longer name is not a reference, and neither * is one followed by `(` (a function call) or by a sheet-qualified name part. * * ⚠ `withoutQuotes` replaces a quoted run with SPACES OF THE SAME LENGTH, which is what makes the * offsets returned here valid against the ORIGINAL string. */ const refSpans = (src: string): RefSpan[] => { const text = withoutQuotes(src); const out: RefSpan[] = []; REF_SPAN_SCAN.lastIndex = 0; let m = REF_SPAN_SCAN.exec(text); while (m) { const start = m.index; const end = start + m[0].length; const before = start > 0 ? text[start - 1] : ""; const after = end < text.length ? text[end] : ""; const namePart = /[A-Za-z0-9_$!'.]/.test(before); const callOrName = after === "(" || /[A-Za-z0-9_.]/.test(after); // β›” THE TEST IS NAMED RATHER THAN INLINED, and that is not a style choice. `verify_formula_ // engine.py`'s W53-T01 family drives a negative control by replacing the COMPILED text of // `referencedAddresses`'s own guard, `if (!namePart && !callOrName)`, and requires it to // occur exactly once in the emitted module. A second copy of that line here would make the // control ambiguous and red a leg that has nothing to do with this ticket. const standalone = !namePart && !callOrName; if (standalone) { out.push({ start, end, colAbs: m[1] === "$", rowAbs: m[3] === "$", col: m[2], row: m[4] }); } m = REF_SPAN_SCAN.exec(text); } return out; }; /** * β›” EXCEL'S FOUR STATES IN EXCEL'S ORDER: `A1`, `$A$1`, `A$1`, `$A1`, and the fourth press is * back where it started. `sheetState.ts::ANCHOR_CYCLE` states the same four for the WHOLE-FORMULA * F4 that a selected (not open) cell gets; this one is per reference, so the two must not drift. */ const ANCHOR_STATES: readonly { colAbs: boolean; rowAbs: boolean }[] = [ { colAbs: false, rowAbs: false }, { colAbs: true, rowAbs: true }, { colAbs: false, rowAbs: true }, { colAbs: true, rowAbs: false }, ]; export interface AnchorCycleResult { /** The whole formula text with ONE reference rewritten. */ text: string; /** Where the caret goes: the END of the rewritten reference. */ caret: number; } /** * ⭐⭐ F4 IN THE OPEN EDITOR: the reference UNDER THE CARET walks one step of the cycle, and * nothing else in the formula moves. `=SUM(A1:A2` with the caret at the end cycles `A2`, leaving * `A1` exactly as it was, which is the whole difference from the whole-formula F4 a closed cell * gets. * * β›”β›” NO REFERENCE UNDER THE CARET MEANS THE NEAREST ONE TO ITS LEFT CYCLES, as Excel does. F2 on * a committed `=SUM(A1:A2)` puts the caret one past `)`, and F4 there cycles `A2`. `null` comes * back only when no reference ends at or before the caret: `=1+2`, a caret before the leading `=`, * a quoted label that merely LOOKS like an address. A reference to the RIGHT of the caret never * cycles, because that edits a character the person has not reached. * * ⭐ THE CARET LANDS AT THE END OF THE REWRITTEN REFERENCE, so a second press finds the SAME * reference and four presses restore the text. Adding a `$` shifts every later character, so a * caret left where it was would drift out of the reference it is cycling. A reference LEFT of the * caret instead keeps the caret at its own place in the text, so four presses restore both. * * ⚠ ADJACENT COUNTS, both sides. `start <= caret <= end`, so a caret resting immediately before * or immediately after a reference cycles it; the spans are disjoint, so at most one qualifies. */ export const cycleAnchorAtCaret = (src: string, caret: number): AnchorCycleResult | null => { const text = String(src ?? ""); const raw = Number.isFinite(caret) ? Math.trunc(caret) : 0; const at = Math.max(0, Math.min(raw, text.length)); let hit: RefSpan | null = null; let left: RefSpan | null = null; for (const span of refSpans(text)) { if (span.start <= at && at <= span.end) hit = span; else if (span.end <= at) left = span; } const under = hit !== null; hit = hit ?? left; if (!hit) return null; const current = ANCHOR_STATES.findIndex( (s) => s.colAbs === hit?.colAbs && s.rowAbs === hit?.rowAbs, ); const next = ANCHOR_STATES[((current < 0 ? 0 : current) + 1) % ANCHOR_STATES.length]; const replacement = (next.colAbs ? "$" : "") + hit.col + (next.rowAbs ? "$" : "") + hit.row; const out: AnchorCycleResult = { text: text.slice(0, hit.start) + replacement + text.slice(hit.end), caret: hit.start + replacement.length, }; // A reference LEFT of the caret keeps the caret where the person put it, shifted by the `$`s. if (!under) out.caret += at - hit.end; return out; }; /** The formula graph, indexed both ways, with every address upper cased exactly once. */ const graphOf = (formulas: Record<string, string>) => { const reads = new Map<string, string[]>(); const readBy = new Map<string, string[]>(); for (const [rawAddr, src] of Object.entries(formulas ?? {})) { const addr = rawAddr.toUpperCase(); const refs = referencedAddresses(src); reads.set(addr, refs); for (const ref of refs) { const list = readBy.get(ref); if (list) list.push(addr); else readBy.set(ref, [addr]); } } return { reads, readBy }; }; /** One workbook formula beside the tab its unqualified references belong to. */ export interface QualifiedFormula { formula: string; sheet: string; style?: Record<string, unknown>; formulaMeta?: Record<string, unknown>; } /** * The workbook graph uses qualified keys while the local graph above remains exactly local. * Names are normalized only at this boundary; callers may retain tab IDs for identity and rebuild * these transient keys after a rename. */ const qualifiedGraphOf = ( formulas: Record<string, QualifiedFormula>, currentSheet: string, ) => { const reads = new Map<string, string[]>(); const readBy = new Map<string, string[]>(); for (const [rawKey, spec] of Object.entries(formulas ?? {})) { const key = rawKey.toUpperCase(); const refs = qualifiedReferencedAddresses(spec.formula, spec.sheet, currentSheet); reads.set(key, refs); for (const ref of refs) { const list = readBy.get(ref); if (list) list.push(key); else readBy.set(ref, [key]); } } return { reads, readBy }; }; /** * Kahn's algorithm over a chosen set of formula nodes. Returns the nodes it could ORDER (a cell * after everything it reads) and the ones it could not, which is the whole of cycle detection: * a node whose in-degree never reaches zero is in a cycle or downstream of one. * * β›” THE READY LIST IS SORTED, SO THE ORDER IS DETERMINISTIC. Two cells that could equally well * be computed first must be computed in the same order on every run, or a gate that pins the * order passes on one machine and fails on the next. */ const kahn = (nodes: Set<string>, reads: Map<string, string[]>) => { const indegree = new Map<string, number>(); const edges = new Map<string, string[]>(); for (const node of nodes) indegree.set(node, 0); for (const node of nodes) { for (const ref of reads.get(node) ?? []) { if (!nodes.has(ref)) continue; indegree.set(node, (indegree.get(node) ?? 0) + 1); const list = edges.get(ref); if (list) list.push(node); else edges.set(ref, [node]); } } const ready = [...nodes].filter((n) => (indegree.get(n) ?? 0) === 0).sort(); const ordered: string[] = []; while (ready.length > 0) { const node = ready.shift() as string; ordered.push(node); for (const dependent of edges.get(node) ?? []) { const left = (indegree.get(dependent) ?? 0) - 1; indegree.set(dependent, left); if (left === 0) { ready.push(dependent); ready.sort(); } } } const settled = new Set(ordered); const blocked = [...nodes].filter((n) => !settled.has(n)).sort(); return { ordered, blocked }; }; /** Kahn's dependency order, followed by every stable-sorted unorderable node. */ const stableCalculationOrder = (nodes: Set<string>, reads: Map<string, string[]>): string[] => { const { ordered, blocked } = kahn(nodes, reads); return [...ordered, ...blocked]; }; /** Every formula node that Kahn's algorithm cannot order. */ const cyclicGraphAddresses = (reads: Map<string, string[]>): string[] => { return kahn(new Set(reads.keys()), reads).blocked; }; /** * ⭐⭐ EVERY FORMULA ADDRESS THAT CANNOT BE ORDERED AT ALL: it refers back to itself, directly * (`A1: =A1`) or round a ring (`A1: =B1`, `B1: =A1`), or it reads one that does. Those cells get * {@link CYCLE_REFUSAL} rather than a number, because there is no number to give and the only * alternatives are a hang and a wrong answer. */ export const cyclicAddresses = (formulas: Record<string, string>): string[] => { const { reads } = graphOf(formulas); return cyclicGraphAddresses(reads); }; /** Workbook-qualified counterpart to {@link cyclicAddresses}; no bare local alias is introduced. */ export const qualifiedCyclicAddresses = ( formulas: Record<string, QualifiedFormula>, currentSheet: string, ): string[] => { const { reads } = qualifiedGraphOf(formulas, currentSheet); return cyclicGraphAddresses(reads); }; /** Every workbook formula in dependency order, followed by the stable unorderable tail. */ export const qualifiedCalculationOrder = ( formulas: Record<string, QualifiedFormula>, currentSheet: string, ): string[] => { const { reads } = qualifiedGraphOf(formulas, currentSheet); return stableCalculationOrder(new Set(reads.keys()), reads); }; /** * ⭐⭐ THE HEART OF INSTRUCTION 11'S SECOND HALF. After `changed` addresses are written, THESE * formula cells must be recomputed, in DEPENDENCY ORDER β€” each one after everything it reads, so * `A1` changing recomputes `B1 (=A1+1)` and then `C1 (=B1*2)`, in that order, in one pass. * Without this a sheet computes `=F6+G6` once and then never again, which is exactly the owner's * "formula is still broken, it only works until I press Refresh". * * β›” THE CHANGED ADDRESSES THEMSELVES ARE NOT IN THE RESULT. Their new cells are what the caller * just built; recomputing them here would either duplicate that work or, for a cell the caller * deliberately cleared, undo it. * * β›” IT TERMINATES ON A CYCLE. Anything unorderable is appended at the END, in a stable sorted * order, and the caller ({@link CYCLE_REFUSAL}'s user, `sheetState.ts::recalcDependents`) states * the refusal for it. A recursive walk would have recursed forever on the same input. */ export const recalcOrder = ( formulas: Record<string, string>, changed: readonly string[], ): string[] => { const { reads, readBy } = graphOf(formulas); const changedSet = new Set((changed ?? []).map((a) => String(a).toUpperCase())); const affected = new Set<string>(); const queue: string[] = [...changedSet]; while (queue.length > 0) { const cur = queue.shift() as string; for (const dependent of readBy.get(cur) ?? []) { if (affected.has(dependent) || changedSet.has(dependent)) continue; affected.add(dependent); queue.push(dependent); } } return stableCalculationOrder(affected, reads); }; /** * Workbook-qualified counterpart to {@link recalcOrder}. A change to bare `A1` on the current * tab can therefore reach `SHEET1!B1`, while a local `B1` on that other tab stays qualified and * cannot be confused with this tab's B1. */ export const qualifiedRecalcOrder = ( formulas: Record<string, QualifiedFormula>, changed: readonly string[], currentSheet: string, ): string[] => { const { reads, readBy } = qualifiedGraphOf(formulas, currentSheet); const changedSet = new Set((changed ?? []).map((key) => String(key).toUpperCase())); const affected = new Set<string>(); const queue: string[] = [...changedSet]; while (queue.length > 0) { const key = queue.shift() as string; for (const dependent of readBy.get(key) ?? []) { if (affected.has(dependent) || changedSet.has(dependent)) continue; affected.add(dependent); queue.push(dependent); } } return stableCalculationOrder(affected, reads); }; // ── evaluation ─────────────────────────────────────────────────────────────────────────────── /** A number in progress, carrying whether any input it used was `pending` (last known, not fresh). */ interface Num { n: number; pending: boolean } // W57-T06: scalar lookup answers keep their text until an operator asks for a number. // Num and the aggregate arithmetic stay intact, including the gate's mutation anchors. type ExpressionValue = Num | CellValue; const expressionNumber = (value: ExpressionValue): Num => "kind" in value ? toNum(value) : value; const expressionCell = (value: ExpressionValue): CellValue => "kind" in value ? value : { kind: "number", value: value.n, pending: value.pending }; // Blank text in scalar arithmetic stays blank, as on the server. Keep this at the operator // boundary: numeric function arguments, aggregates, comparisons and genuinely empty cells // retain their own coercion rules. Text also carries the blank through subsequent arithmetic. const arithmeticBlank = (...values: ExpressionValue[]): CellValue | null => { if (!values.some((value) => "kind" in value && value.kind === "text" && value.value.trim() === "")) return null; for (const value of values) { if ("kind" in value && (value.kind === "error" || value.kind === "unsupported")) toNum(value); } const pending = values.some((value) => ("kind" in value && value.kind === "pending") || ("pending" in value && value.pending === true)); return { kind: "text", value: "", ...(pending ? { pending: true } : {}) }; }; const toNum = (cv: CellValue): Num => { if (cv.kind === "number") return { n: cv.value, pending: cv.pending === true }; if (cv.kind === "boolean") return { n: Number(cv.value), pending: cv.pending === true }; if (cv.kind === "text") { const trimmed = cv.value.trim(); if (trimmed === "") return { n: 0, pending: cv.pending === true }; const n = Number(trimmed.replace(/,/g, "")); if (!Number.isFinite(n)) throw new FormulaError("This formula expects a number here."); return { n, pending: cv.pending === true }; } if (cv.kind === "empty") return { n: 0, pending: false }; // β›”β›” THE TRAP THE TICKET NAMES. A cell with no value at all yet never blocks arithmetic: it // reads as zero, WITH `pending: true` riding up so the whole formula can mark itself the same // way and recompute the moment the real value arrives. if (cv.kind === "pending") return { n: 0, pending: true }; if (cv.kind === "unsupported") throw new Unsupported(cv.reason); throw new FormulaError(cv.message, cv); }; interface Cursor { tokens: Token[]; i: number } const peekTok = (c: Cursor): Token | undefined => c.tokens[c.i]; const atEnd = (c: Cursor): boolean => c.i >= c.tokens.length; const take = (c: Cursor): Token => { const tk = c.tokens[c.i]; if (!tk) throw new Unsupported("ended early"); c.i += 1; return tk; }; const isOp = (tk: Token | undefined, v: string): boolean => !!tk && tk.t === "op" && tk.v === v; export type FormulaGetter = ((addr: string) => CellValue) & { workbookReferences?: true }; type Getter = FormulaGetter; /** * Mark a getter as able to resolve the full `SHEET!ADDR` key. * * β›” THIS OPT IN IS THE WALL BETWEEN A WORKBOOK LOOKUP AND A LOCAL LOOKUP. Qualified syntax fed * to an ordinary getter remains `unsupported`; otherwise an older caller would accept the syntax * and answer it from a same-named local cell with no error. */ export const workbookFormulaGetter = (read: (addr: string) => CellValue): Getter => { const get: Getter = (addr: string) => read(addr); get.workbookReferences = true; return get; }; // ── W54-T27 / RULING R9(iii): AN AGGREGATE IGNORES TEXT, AND `COUNT` IS NOT `COUNTA` ────────── // // ⭐⭐ WHAT WAS WRONG, MEASURED IN A BROWSER BEFORE THIS TICKET. A column with a HEADER in it, // which is the single most common shape a spreadsheet has, broke every aggregate below it: with // a label in A1 and two numbers under it, all five of `SUM`, `COUNT`, `AVG`, `MIN` and `MAX` // answered a refusal rather than a number. The cause was one line: a non blank, non numeric cell // went through the same numeric coercion an operand does, and that coercion states a wrong. // // β›” SO A RANGE IS FILTERED, NOT COERCED. An aggregate reads a REGION, and a region is expected // to hold labels beside its numbers. An operand is a single value somebody wrote INTO the // formula, and a label there really is a mistake, so `=A1+1` over a label is still a stated // wrong. That asymmetry is Excel's and it is the whole distinction this file now draws. // // β›” AND AN ERROR IS NOT A LABEL, SO THE FOUR THAT COMPUTE PROPAGATE IT. `SUM`, `AVG`, `MIN` and // `MAX` answer with the ORIGINAL fault of any cell in their range, because a total that quietly // skipped a broken cell would be a confidently wrong number, which is worse than a refusal. The // two that COUNT never adopt it: they are measuring how full a region is, not computing with it, // so `COUNT` ignores an error (it is not a number) and `COUNTA` counts it (it is not empty). // Excel answers all six of those exactly this way. /** * ⭐ THE TWO THAT MEASURE A REGION RATHER THAN COMPUTING OVER IT, said once so the error rule and * the switch below cannot drift apart. */ const COUNTING_FUNCTIONS: ReadonlySet<string> = new Set<string>(["COUNT", COUNTA_FUNCTION]); const evalAggregate = (name: string, addrs: string[], get: Getter): Num => { const nums: number[] = []; let pending = false; let filled = 0; const counting = COUNTING_FUNCTIONS.has(name); for (const addr of addrs) { const cv = get(addr); if (cv.kind === "empty") continue; if (cv.kind === "pending") { pending = true; continue; } if (cv.kind === "unsupported") throw new Unsupported(cv.reason); if (cv.kind === "error") { if (!counting) throw new FormulaError(cv.message, cv); filled += 1; continue; } if (cv.kind === "text") { // ⭐⭐ THE OWNER'S SENTENCE, IN ONE BRANCH: a blank is nothing at all and a label is // something that is not a number. Neither reaches the arithmetic; only the second is a // non empty cell, so only the second is counted by `COUNTA`. if (cv.value.trim() === "") continue; pending = pending || cv.pending === true; filled += 1; continue; } pending = pending || cv.pending === true; filled += 1; if (cv.kind === "number") nums.push(cv.value); } const sum = nums.reduce((a, b) => a + b, 0); switch (name) { case "SUM": return { n: sum, pending }; case "COUNT": return { n: nums.length, pending }; case "COUNTA": return { n: filled, pending }; case "AVG": return { n: nums.length ? sum / nums.length : 0, pending }; case "MIN": return { n: nums.length ? Math.min(...nums) : 0, pending }; case "MAX": return { n: nums.length ? Math.max(...nums) : 0, pending }; default: throw new Unsupported(name); } }; /** * ⭐⭐ W54-T27 / D-678: A RANGE ENDPOINT MAY BE AN ERROR LITERAL, AND THEN THE CALL IS THAT ERROR. * A structural delete leaves `#REF!` written into the formula text, so `=SUM(#REF!)` is a real * thing a workbook holds. Demanding a `ref : ref` pair answered `unsupported` for it, which sent * a formula the server cannot mend any further off on a round trip to be told the same thing. * Excel answers `#REF!`, and so does this now. */ const rangeEndpoint = (tok: Token, name: string): string => { if (tok.t === "err") throw new FormulaError(tok.v); if (tok.t !== "ref") throw new Unsupported(name); return tok.v; }; // W57-T06: retain a rectangle's shape beside the shared row-major address expansion. // Whole bands, named ranges and array-producing expressions still belong to Refresh. interface LookupRange { kind: "range"; addrs: string[]; rows: number; cols: number; explicit: boolean } interface LookupBoolean { kind: "logical"; literal: "TRUE" | "FALSE" } type LookupArgument = CellValue | LookupRange | LookupBoolean; // W57-T07: one dispatch table and argument loop for lookups, text and dates. TEXTJOIN's // variadic cap is formula_runtime.py's MANY (30); larger calls defer to its arity refusal. const CALL_ARITY: Readonly<Record<string, readonly [number, number]>> = { XLOOKUP: [3, 5], VLOOKUP: [3, 4], INDEX: [2, 3], MATCH: [2, 3], CHOOSE: [2, 255], TEXTJOIN: [3, 30], SUBSTITUTE: [3, 4], EOMONTH: [2, 2], WORKDAY: [2, 3], NETWORKDAYS: [2, 3], }; const parseLookupArgument = ( c: Cursor, get: Getter, booleanFlag: string | undefined, booleanLiterals = false, ): LookupArgument => { const first = peekTok(c); const next = c.tokens[c.i + 1]; // W57-T07: reuse W57-T06's real-boolean flag for TEXTJOIN. Text arguments may also spell // TRUE/FALSE for the server's _text conversion; a bare name outside these calls is unchanged. if (booleanFlag || booleanLiterals) { if (first?.t === "fn" && (first.v === "FALSE" || first.v === "TRUE") && (isOp(next, ",") || isOp(next, ")"))) { take(c); return { kind: "logical", literal: first.v }; } // W57-T06: the server takes a boolean and refuses a number. Numeric flags and // expressions must reach the server, even when they would evaluate to zero here. if (booleanFlag) throw new Unsupported(booleanFlag); } if (first?.t === "ref" && (isOp(next, ":") || isOp(next, ",") || isOp(next, ")"))) { take(c); let last = first.v; if (isOp(peekTok(c), ":")) { take(c); last = rangeEndpoint(take(c), "lookup range"); } const a = splitRef(first.v); const b = splitRef(last); return { kind: "range", addrs: rangeAddresses(first.v, last), explicit: isOp(next, ":"), rows: Math.abs(a.row - b.row) + 1, cols: Math.abs(a.col - b.col) + 1 }; } // W57-T06: recurse through the expression grammar; nested calls and 1+2 keep precedence. return expressionCell(parseExpr(c, get)); }; // W57-T06: match the server's _values_equal (numeric strings, then case-insensitive text). // Do not use arithmetic's comma-stripping coercion for lookup keys: "1,000" is text there. const lookupKey = (value: CellValue): number | string => { if (value.kind === "number") { if (!Number.isFinite(value.value)) throw new Unsupported("lookup number"); return value.value; } if (value.kind === "empty") return ""; if (value.kind === "pending") throw new PendingInput("lookup key"); if (value.kind !== "text") { toNum(value); throw new Unsupported("lookup key"); } const trimmed = value.value.trim(); if (/^[+-]?(?:\d(?:_?\d)*(?:\.(?:\d(?:_?\d)*)?)?|\.\d(?:_?\d)*)(?:[eE][+-]?\d(?:_?\d)*)?$/.test(trimmed)) { const n = Number(trimmed.replace(/_/g, "")); if (Number.isFinite(n)) return n; } // Python casefold and JavaScript lowercase differ for some Unicode text. Defer those // comparisons instead of presenting an answer that can change at Refresh. if (/[^\x00-\x7f]/.test(value.value)) throw new Unsupported("lookup Unicode comparison"); return value.value.toLowerCase(); }; // W57-T07: mirror _text without arithmetic coercion. Python's float spelling differs for // tiny fractions and large integers; defer those instead of changing text after Refresh. const functionText = (value: CellValue): string => { if (value.kind === "text") return value.value; if (value.kind === "empty") return ""; if (value.kind === "pending") throw new PendingInput("text argument"); if (value.kind === "number") { const n = value.value; if (!Number.isFinite(n) || (Number.isInteger(n) && !Number.isSafeInteger(n)) || (n !== 0 && Math.abs(n) < 0.0001)) throw new Unsupported("text number spelling"); return String(n); } toNum(value); throw new Unsupported("text argument"); }; // W57-T07: _num accepts decimal numeric text, including exponents and underscores, but // never arithmetic's comma stripping, hex or blank-as-zero. Unicode numerals defer because // Python float accepts spellings JavaScript Number does not. Null mirrors _num's no-number. const functionNumber = (value: CellValue): number | null => { if (value.kind === "pending") throw new PendingInput("numeric argument"); if (value.kind === "empty") return null; if (value.kind === "number") return Number.isFinite(value.value) ? value.value : null; if (value.kind !== "text") { toNum(value); throw new Unsupported("numeric argument"); } if (/[^\x00-\x7f]/.test(value.value)) throw new Unsupported("numeric Unicode argument"); const trimmed = value.value.trim(); if (!/^[+-]?(?:\d(?:_?\d)*(?:\.(?:\d(?:_?\d)*)?)?|\.\d(?:_?\d)*)(?:[eE][+-]?\d(?:_?\d)*)?$/.test(trimmed)) return null; const n = Number(trimmed.replace(/_/g, "")); return Number.isFinite(n) ? n : null; }; const MAX_DAY_SPAN = 20_000; const CALENDAR_DAY_MS = 86_400_000; // W57-T07: dates are ISO strings, never serials or local time. Use UTC for every calendar // operation so a reader west of Greenwich cannot see the previous date. setUTCFullYear also // avoids Date.UTC's special 1900 offset for years 0 through 99. const utcCalendarDate = (year: number, month: number, day: number): Date => { const date = new Date(0); date.setUTCFullYear(year, month - 1, day); return date; }; const functionDate = (value: CellValue): Date | null => { if (value.kind === "pending") throw new PendingInput("date argument"); if (value.kind === "error" || value.kind === "unsupported") toNum(value); if (value.kind !== "text") return null; // W57-T07: _date reads only the first ten characters, accepting timestamp suffixes. // Other ISO spellings remain server-owned rather than using JavaScript's loose parser. const prefix = value.value.slice(0, 10); if (!/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/.test(prefix)) return null; const year = Number(prefix.slice(0, 4)); const month = Number(prefix.slice(5, 7)); const day = Number(prefix.slice(8, 10)); if (year < 1 || month < 1 || month > 12 || day < 1 || day > 31) return null; const date = utcCalendarDate(year, month, day); return date.getUTCFullYear() === year && date.getUTCMonth() === month - 1 && date.getUTCDate() === day ? date : null; }; const functionDateText = (date: Date): string => { // W57-T07: Python dates stop at years 1..9999; its overflow outcome belongs to Refresh. const year = date.getUTCFullYear(); if (!Number.isFinite(year) || year < 1 || year > 9999) throw new Unsupported("date overflow"); return date.toISOString().slice(0, 10); }; /** * ⭐⭐ HOTFIX 2026-09-24: `formula_runtime.py`'s ISO DAY ARITHMETIC, MIRRORED (its `+`/`-` branch). * A date is ISO text on both sides (`DATE` and `EOMONTH` already answer it), and the server gives * that text Excel's day arithmetic: date minus date is a day count, date plus or minus a whole * number is another ISO date. The client read the same text through `toNum`, so the Daily Sales * workbook's `=F7-6` and `=(DATE(YEAR(R7),12,31) - R7)/7` painted `#VALUE!` over values the server * stores as `2026-06-05` and `17`. Sheet inputs read as the server's `_SheetInputs` read them: a * blank is 0 and a boolean is 1 or 0. `null` means no date rule applies and arithmetic proceeds. */ const isoDayArithmetic = (l: CellValue, r: CellValue, op: "+" | "-"): ExpressionValue | null => { const leftDay = l.kind === "text" ? functionDate(l) : null; const rightDay = r.kind === "text" ? functionDate(r) : null; if (!leftDay && !rightDay) return null; if (l.kind === "pending" || r.kind === "pending") throw new PendingInput("date arithmetic"); const numberOf = (v: CellValue): number | null => v.kind === "empty" ? 0 : v.kind === "boolean" ? Number(v.value) : v.kind === "number" || v.kind === "text" ? functionNumber(v) : null; const pending = ("pending" in l && l.pending === true) || ("pending" in r && r.pending === true); const shifted = (day: Date, days: number): ExpressionValue => ({ kind: "text", value: functionDateText(new Date(day.getTime() + days * CALENDAR_DAY_MS)), ...(pending ? { pending: true } : {}), }); if (leftDay && rightDay && op === "-") { return { n: Math.round((leftDay.getTime() - rightDay.getTime()) / CALENDAR_DAY_MS), pending }; } const rightNum = numberOf(r); if (leftDay && rightNum !== null && Number.isInteger(rightNum)) { return shifted(leftDay, op === "+" ? rightNum : -rightNum); } const leftNum = numberOf(l); if (rightDay && leftNum !== null && op === "+" && Number.isInteger(leftNum)) { return shifted(rightDay, leftNum); } return null; }; const parseLookup = (name: string, c: Cursor, get: Getter, prepared?: (read: Getter) => LookupArgument[]): CellValue => { const textOrDate = name === "TEXTJOIN" || name === "SUBSTITUTE" || name === "EOMONTH" || name === "WORKDAY" || name === "NETWORKDAYS"; let pending = false; const observe = (value: CellValue): CellValue => { if (value.kind === "pending" || ((value.kind === "number" || value.kind === "text") && value.pending)) pending = true; return value; }; // W57-T06: every actual read, including a preceding non-match or an argument expression, // contributes its pending flag. Preserve workbook qualification on the wrapped getter. const read: Getter = (addr) => observe(get(addr)); if (get.workbookReferences) read.workbookReferences = true; const args: LookupArgument[] = prepared ? prepared(read) : []; if (!prepared) { take(c); // Opening parenthesis already checked by parseAtom. if (isOp(peekTok(c), ")")) throw new Unsupported(name + " arguments"); while (true) { const booleanFlag = name === "VLOOKUP" && args.length === 3 ? "VLOOKUP approximate match" : name === "TEXTJOIN" && args.length === 1 ? "TEXTJOIN ignore_empty" : undefined; args.push(parseLookupArgument(c, read, booleanFlag, textOrDate)); if (isOp(peekTok(c), ")")) { take(c); break; } if (!isOp(peekTok(c), ",")) throw new Unsupported(name + " arguments"); take(c); } } const [min, max] = CALL_ARITY[name]; if (args.length < min || args.length > max) throw new Unsupported(name + " arguments"); const scalar = (index: number): CellValue => { const arg = args[index]; if (arg.kind === "logical") throw new Unsupported(name + " scalar argument"); if (arg.kind !== "range") return observe(arg); // W57-T07: A1 is a scalar read, but even A1:A1 is explicit range syntax. Defer ranges // at new scalar positions instead of flattening them and shifting argument meanings. if (textOrDate && arg.explicit) throw new Unsupported(name + " scalar argument"); if (arg.addrs.length !== 1) throw new Unsupported(name + " scalar argument"); return read(arg.addrs[0]); }; const number = (index: number): number => { const value = scalar(index); if (value.kind === "pending") throw new PendingInput("lookup argument"); const numeric = toNum(value); pending = pending || numeric.pending; if (!Number.isFinite(numeric.n)) throw new Unsupported(name + " numeric argument"); return numeric.n; }; const range = (index: number): LookupRange => { const arg = args[index]; if (arg.kind !== "range") throw new Unsupported(name + " range argument"); return arg; }; const finish = (value: CellValue): CellValue => { observe(value); // W57-T06: propagate a selected error here, after a nested aggregate has had its own // chance to ignore error cells for COUNT/COUNTA. The tracking getter must not throw. if (value.kind === "error" || value.kind === "unsupported") toNum(value); if (pending && (value.kind === "number" || value.kind === "text")) { return { ...value, pending: true }; } return pending && value.kind === "empty" ? { kind: "pending" } : value; }; const noMatch = (): never => { if (pending) throw new PendingInput("lookup match"); // W57-T06: Excel's #N/A is outside the shared four-code vocabulary. This deliberate // deferral lets the server report no_match without inventing a fifth client error code. throw new Unsupported(name + " no match (#N/A)"); }; if (name === "TEXTJOIN" || name === "SUBSTITUTE") { const text = (index: number): string => { const arg = args[index]; return arg.kind === "logical" ? arg.literal : functionText(scalar(index)); }; if (name === "TEXTJOIN") { const delimiter = text(0); const flag = args[1]; if (flag.kind !== "logical") throw new Unsupported("TEXTJOIN ignore_empty"); const parts: string[] = []; for (let i = 2; i < args.length; i += 1) { const part = text(i); if (flag.literal === "FALSE" || part !== "") parts.push(part); } return finish({ kind: "text", value: parts.join(delimiter) }); } const source = text(0); const old = text(1); const replacement = text(2); // W57-T07: Python inserts around characters for replace("", ...), but split("") raises // for an instance request. Defer both empty separators rather than inventing that result. if (old === "") throw new Unsupported("SUBSTITUTE empty old_text"); const parts = source.split(old); if (args.length === 3) return finish({ kind: "text", value: parts.join(replacement) }); const instance = args[3].kind === "logical" ? null : functionNumber(scalar(3)); const occurrence = Math.trunc(instance ?? 0); // W57-T07: reproduce the server's Python slices even for zero/negative occurrences; // Excel's invalid-instance rules would give a different answer on the next Refresh. const value = parts.slice(0, occurrence).join(old) + (occurrence > 0 && occurrence < parts.length ? replacement + parts.slice(occurrence).join(old) : ""); return finish({ kind: "text", value }); } if (name === "EOMONTH" || name === "WORKDAY" || name === "NETWORKDAYS") { const date = (index: number): Date => { const parsed = functionDate(scalar(index)); // W57-T07: _date returns None without bad_value. Refresh owns the resulting blank or // refusal; do not label an unparseable date #VALUE! just because JavaScript rejected it. if (!parsed) throw new Unsupported(name + " date argument"); return parsed; }; const strictNumber = (index: number): number => { const n = functionNumber(scalar(index)); // W57-T07: _strict_num distinguishes blank from bad_value. Defer either no-number // case so arithmetic's zero coercion cannot turn a missing offset into a real date. if (n === null) throw new Unsupported(name + " numeric argument"); return Math.trunc(n); }; const start = date(0); if (name === "EOMONTH") { const months = strictNumber(1); const index = start.getUTCMonth() + months; const year = start.getUTCFullYear() + Math.floor(index / 12); const month = ((index % 12) + 12) % 12 + 1; // W57-T07: _month_shift clamps the original day, then EOMONTH discards it. Day zero // of the following month gives that same last day, including leap years and negatives. if (!Number.isSafeInteger(months) || year < 1 || year > 9999) { throw new Unsupported("EOMONTH date overflow"); } return finish({ kind: "text", value: functionDateText(utcCalendarDate(year, month + 1, 0)) }); } const end = name === "NETWORKDAYS" ? date(1) : null; const days = end ? (end.getTime() - start.getTime()) / CALENDAR_DAY_MS : strictNumber(1); if (Math.abs(days) > MAX_DAY_SPAN) { throw new FormulaError(ERROR_VALUE + " Working day span exceeds 20000 days."); } const holidays = new Set<number>(); if (args.length > 2) { const arg = args[2]; // W57-T07: the server accepts a holiday range or scalar and ignores invalid entries. // Read every range member through read so even a pending holiday marks the answer. if (arg.kind !== "logical") { const values = arg.kind === "range" ? arg.addrs.map(read) : [scalar(2)]; for (const value of values) { const parsed = functionDate(value); // W57-T07: a non-ISO holiday (including an integer YYYYMMDD) might be a spelling // Python accepts. Defer nonblank invalid entries instead of dropping a holiday. if (!parsed && value.kind !== "empty" && !(value.kind === "text" && value.value === "")) { throw new Unsupported(name + " holiday date"); } if (parsed) holidays.add(parsed.getTime()); } } } const working = (cursor: Date): boolean => cursor.getUTCDay() !== 0 && cursor.getUTCDay() !== 6 && !holidays.has(cursor.getTime()); const step = days >= 0 ? 1 : -1; const cursor = new Date(start.getTime()); if (end) { let counted = 0; // W57-T07: both ends count, including the start when dates run backwards. while (true) { if (working(cursor)) counted += 1; if (cursor.getTime() === end.getTime()) break; cursor.setUTCDate(cursor.getUTCDate() + step); } return finish({ kind: "number", value: counted === 0 ? 0 : step * counted }); } // W57-T07: WORKDAY steps before testing; zero days returns the start even on a weekend. let remaining = Math.abs(days); while (remaining > 0) { cursor.setUTCDate(cursor.getUTCDate() + step); if (cursor.getUTCFullYear() < 1 || cursor.getUTCFullYear() > 9999) { throw new Unsupported("WORKDAY date overflow"); } if (working(cursor)) remaining -= 1; } return finish({ kind: "text", value: functionDateText(cursor) }); } if (name === "CHOOSE") { const index = Math.trunc(number(0)); if (index < 1 || index >= args.length) { throw new FormulaError(ERROR_VALUE + " CHOOSE index is outside its list."); } return finish(scalar(index)); } if (name === "INDEX") { const array = range(0); let row = Math.trunc(number(1)); let col = args.length > 2 ? Math.trunc(number(2)) : 1; if (args.length === 2) { if (array.rows === 1) { col = row; row = 1; } else if (array.cols !== 1) throw new Unsupported("INDEX array result"); } if (row < 1 || row > array.rows || col < 1 || col > array.cols) { // W57-T06: `#VALUE!` rather than Excel's `#REF!`, on purpose. `formula_runtime.py` answers // `bad_value` for a position outside the range and `types.ts` maps that to `#VALUE!`, and the // server recomputes every formula at Refresh. Matching Excel here would make the token change // under the reader a moment later. Excel parity is the SERVER's to fix, in one place, later. throw new FormulaError(ERROR_VALUE + " INDEX position is outside its range."); } return finish(read(array.addrs[(row - 1) * array.cols + col - 1])); } const wanted = lookupKey(scalar(0)); const array = range(1); let resultRange: LookupRange | undefined; let col = 1; // W57-T06: approximate modes require sorted-list and ordering parity beyond the worked // cases. Keep VLOOKUP's TRUE/default, MATCH's +/-1/default and nonzero XLOOKUP modes on // the server instead of silently treating an approximate request as an exact request. if (name === "VLOOKUP") { const flag = args[3]; if (flag?.kind !== "logical" || flag.literal !== "FALSE") throw new Unsupported("VLOOKUP approximate match"); col = Math.trunc(number(2)); if (col < 1) throw new FormulaError(ERROR_VALUE + " VLOOKUP column must be at least one."); // W57-T06: the same server bad_value bounds mapping as INDEX above. if (col > array.cols) throw new FormulaError(ERROR_VALUE + " VLOOKUP column is outside its table."); } else { if (array.rows !== 1 && array.cols !== 1) throw new Unsupported(name + " lookup vector"); if (name === "MATCH") { if (args.length < 3 || number(2) !== 0) throw new Unsupported("MATCH approximate match"); } else { resultRange = range(2); if (resultRange.rows !== 1 && resultRange.cols !== 1) throw new Unsupported("XLOOKUP array result"); if (array.addrs.length !== resultRange.addrs.length) { throw new FormulaError(ERROR_VALUE + " XLOOKUP ranges must have the same size."); } if (args.length > 4 && number(4) !== 0) throw new Unsupported("XLOOKUP match mode"); } } const count = name === "VLOOKUP" ? array.rows : array.addrs.length; for (let i = 0; i < count; i += 1) { const offset = name === "VLOOKUP" ? i * array.cols : i; if (lookupKey(read(array.addrs[offset])) !== wanted) continue; if (name === "MATCH") return finish({ kind: "number", value: i + 1 }); const address = resultRange ? resultRange.addrs[i] : array.addrs[offset + col - 1]; return finish(read(address)); } if (name === "XLOOKUP" && args.length > 3) { const fallback = scalar(3); if (fallback.kind !== "empty") return finish(fallback); } return noMatch(); }; const parseAtom = (c: Cursor, get: Getter): ExpressionValue => { const tk = take(c); if (tk.t === "num") return { n: tk.v, pending: false }; if (tk.t === "ref") return get(tk.v); // ⭐⭐ W54-T26: AN ERROR PROPAGATES THROUGH ARITHMETIC, EXACTLY AS EXCEL DOES. Reading one is // the same as reading a cell that holds one, so it takes the same road out: a thrown // `FormulaError` whose message already states the code, which `errorValue` passes through. if (tk.t === "err") throw new FormulaError(tk.v); // ⭐ W54-T26: a text literal coerces the way a text CELL does, so `=1+"1"` is 2 and `=1+"x"` // is `#VALUE!`, which is Excel's own answer to both. if (tk.t === "str") return { kind: "text", value: tk.v }; if (tk.t === "fn") { const name = tk.v; // ⭐⭐ W54-T26: A BARE NAME IS NOT A CALL AND NOTHING CAN RESOLVE IT. `=FOO` names a thing, // and NEITHER engine has named ranges, so no round trip to the server can turn it into a // value. That makes it the one name this file may refuse on its own: `#NAME?`. if (!isOp(peekTok(c), "(")) throw new FormulaError(NAME_NOT_FOUND); if (Object.prototype.hasOwnProperty.call(CALL_ARITY, name)) return parseLookup(name, c, get); // ⭐ EVERY OTHER FUNCTION IN THE CATALOGUE IS THE SERVER'S. `IF`, `ROUND`, `TEXT` and the // rest are real formulas this file does not speak, so they take the `unsupported` road home // and keep working exactly as they did before this ticket (see the header). // β›” WHICH IS WHY AN UNKNOWN CALL IS NOT `#NAME?` HERE. The server owns the catalogue; it // answers `bad_formula` for a name no catalogue has, and `types.ts` reads THAT as `#NAME?`. if (!AGGREGATE_SET.has(name)) throw new Unsupported(name); take(c); const from = rangeEndpoint(take(c), name); // W57-T03 / P1 F2: one cell is a legal aggregate input in Excel and on the server. // COUNT(A6) and SUM(A6) can compute here without waiting for Refresh. Only one cell or // the existing range is admitted; argument lists and whole-band references stay unsupported. if (isOp(peekTok(c), ")")) { take(c); return evalAggregate(name, [from], get); } if (!isOp(peekTok(c), ":")) throw new Unsupported(name); take(c); const to = rangeEndpoint(take(c), name); if (!isOp(peekTok(c), ")")) throw new Unsupported(name); take(c); return evalAggregate(name, rangeAddresses(from, to), get); } if (tk.t === "op" && tk.v === "(") { const inner = parseExpr(c, get); if (!isOp(peekTok(c), ")")) throw new Unsupported("parenthesis"); take(c); return inner; } throw new Unsupported("token"); }; const parseUnary = (c: Cursor, get: Getter): ExpressionValue => { if (isOp(peekTok(c), "-")) { take(c); const value = parseUnary(c, get); const blank = arithmeticBlank(value); if (blank) return blank; const inner = expressionNumber(value); return { n: -inner.n, pending: inner.pending }; } if (isOp(peekTok(c), "+")) { take(c); return expressionNumber(parseUnary(c, get)); } return parseAtom(c, get); }; const parseTerm = (c: Cursor, get: Getter): ExpressionValue => { let value = parseUnary(c, get); while (isOp(peekTok(c), "*") || isOp(peekTok(c), "/")) { const op = take(c); const term = parseUnary(c, get); const blank = arithmeticBlank(value, term); if (blank) { value = blank; continue; } const left = expressionNumber(value); const right = expressionNumber(term); if (op.t === "op" && op.v === "/") { // β›” A DIVISOR THAT IS ONLY ZERO BECAUSE IT HAS NOT LOADED IS NOT A DIVISION BY ZERO. It is // a value nobody has yet, so the whole formula waits rather than accusing the reader of // dividing by something they never wrote. if (right.n === 0 && right.pending) throw new PendingInput("divisor"); if (right.n === 0) throw new FormulaError("This formula divides by zero."); value = { n: left.n / right.n, pending: left.pending || right.pending }; } else { value = { n: left.n * right.n, pending: left.pending || right.pending }; } } return value; }; const parseExpr = (c: Cursor, get: Getter): ExpressionValue => { let value = parseTerm(c, get); while (isOp(peekTok(c), "+") || isOp(peekTok(c), "-")) { const op = take(c); const term = parseTerm(c, get); const plus = op.t === "op" && op.v === "+"; const blank = arithmeticBlank(value, term); if (blank) { value = blank; continue; } // HOTFIX 2026-09-24: THIS path answers plain `=F7-6`, so it takes the same ISO day rule as the // tree evaluator's binary branch; missing it here is what left Daily Sales E6 at #VALUE!. const day = isoDayArithmetic(expressionCell(value), expressionCell(term), plus ? "+" : "-"); if (day) { value = day; continue; } const left = expressionNumber(value); const right = expressionNumber(term); value = { n: plus ? left.n + right.n : left.n - right.n, pending: left.pending || right.pending }; } return value; }; /** * ⚠ BINARY FLOATING POINT'S OWN NOISE, TRIMMED AT THE LAST PLACE IT CAN BE TRUSTED. `0.1 + 0.2` * is `0.30000000000000004` in every language that uses doubles, and a spreadsheet that paints * that reads as broken. 15 significant digits is the widest a double round trips exactly, so this * removes the noise and nothing else. */ const cleanNumber = (n: number): number => { if (!Number.isFinite(n) || Number.isInteger(n)) return n; return Number(n.toPrecision(15)); }; /** * ⭐⭐ THE ONE ENTRY POINT, CONTRACT C1. `src` may or may not carry its leading `=`; both are * accepted so a caller can hand this either the raw stored formula or its own trimmed draft. * * ⭐ A BARE SINGLE REFERENCE PASSES THROUGH UNCOERCED. `=F6` of a text cell is F6's text, not a * refusal. W57-T06 also passes scalar lookup text through; only numeric arguments and * arithmetic operators request coercion. * * β›” NEVER THROWS. Every outcome β€” a token this engine does not speak, a function that belongs to * the server, a division by zero, a reference to a cell held in a genuine error state β€” comes * back as a `CellValue`, because a formula cell's caller (`sheetState.ts::setFormula`) has * exactly one door for each of them and it is a value, not an exception. */ export const evaluateFormula = (src: string, get: Getter): CellValue => { try { const tokens = tokenize(src, get.workbookReferences === true); if (tokens.length === 0) return { kind: "empty" }; // Keep both client entry points on the same implementation for these server functions. if (tokens.some((token) => token.t === "fn" && (SUMMARY_FUNCTION_ARITY[token.v] || token.v === "AVG" || token.v === "AVERAGE"))) { const result = { ...evaluateCompiledSheetFormula(compileSheetFormula(src), get) }; // The legacy entry point returns values without the workbook renderer's metadata. delete result.style; delete result.formulaMeta; if ("pending" in result && result.pending === false) delete result.pending; return result; } // ⭐ W54-T26: `="hello"` IS ITS OWN TEXT, not a wrong-typed argument. Only once an operator // or a function is involved does a literal coerce, which is the same distinction the bare // reference below already draws. const only = tokens.length === 1 ? tokens[0] : null; if (only && only.t === "str") return { kind: "text", value: only.v }; if (tokens.length === 1 && tokens[0].t === "ref") { const one = tokens[0]; return one.t === "ref" ? get(one.v) : { kind: "empty" }; } const cursor: Cursor = { tokens, i: 0 }; const expression = parseExpr(cursor, get); if (!atEnd(cursor)) throw new Unsupported("trailing tokens"); // W57-T06: a lookup's text or empty result reaches the cell without numeric coercion. // Arithmetic has already called expressionNumber, which reuses the existing toNum rules. if ("kind" in expression && (expression.kind === "text" || expression.kind === "empty" || expression.kind === "pending")) return expression; const result = expressionNumber(expression); if (!Number.isFinite(result.n)) { return { kind: "error", message: "This formula could not be calculated." }; } const value = cleanNumber(result.n); return result.pending ? { kind: "number", value, pending: true } : { kind: "number", value }; } catch (err) { if (err instanceof PendingInput) return { kind: "pending" }; if (err instanceof Unsupported) return { kind: "unsupported", reason: err.message }; // ⭐⭐ W54-T26: THE ONE DOOR AN ERROR LEAVES BY, AND IT LEAVES AS A VALUE WITH A CODE. if (err instanceof FormulaError) return errorValue(err.message); return { kind: "error", message: "This formula could not be calculated." }; } }; /** C8 rectangle contract: sheet, inclusive one-based columns, inclusive one-based rows. * Python's compact `parse_formula` publishes the same tuple and ordered precedent variants. * A rectangle is one subscription, including a whole row or column; blanks are never edges. */ export type FormulaRange = readonly [string, number, number, number, number]; export type FormulaPrecedent = { readonly cell: string } | { readonly range: FormulaRange }; export type SheetSyntax = | { readonly t: "num"; readonly v: number } | { readonly t: "str"; readonly v: string } | { readonly t: "ref"; readonly v: string } | { readonly t: "boolean"; readonly v: string } | { readonly t: "err"; readonly v: FormulaErrorCode } | { readonly t: "cycle" } | { readonly t: "range"; readonly bounds: FormulaRange; readonly explicit: boolean } | { readonly t: "name"; readonly token: string; readonly e: SheetSyntax } | { readonly t: "spill"; readonly e: SheetSyntax } | { readonly t: "refop"; readonly op: string; readonly l: SheetSyntax; readonly r: SheetSyntax } | { readonly t: "unary"; readonly op: string; readonly e: SheetSyntax } | { readonly t: "binary"; readonly op: string; readonly l: SheetSyntax; readonly r: SheetSyntax } | { readonly t: "call"; readonly name: string; readonly args: readonly SheetSyntax[] }; export interface CompiledSheetFormula { readonly source: string; readonly syntax: SheetSyntax | null; readonly precedents: readonly FormulaPrecedent[]; readonly cells: readonly string[]; readonly ranges: readonly FormulaRange[]; readonly unsupported?: string; readonly volatile: boolean; readonly ownSheet: string; readonly currentSheet: string; readonly namedRanges: readonly NamedFormulaRange[]; readonly address: string; } export interface NamedFormulaRange { name: string; ref: string; sheet?: string; hidden?: boolean; base?: string } export const normalizeNamedFormulaRanges = (value: unknown): NamedFormulaRange[] => { const entries = Array.isArray(value) ? value : value && typeof value === "object" ? Object.entries(value).map(([name, ref]) => typeof ref === "string" ? { name, ref } : ref && typeof ref === "object" ? { name, ...ref } : null) : []; return entries.filter((entry): entry is NamedFormulaRange => Boolean(entry && typeof entry.name === "string" && typeof entry.ref === "string")).map((entry) => ({ ...entry })); }; export interface CalculationOptions { namedRanges?: readonly NamedFormulaRange[]; dateSystem?: "1900" | "1904"; sheets?: readonly string[]; spillChildren?: Record<string, SpillChildMarker>; cellStyles?: Record<string, Record<string, unknown>>; } export interface SpillChildMarker { version: number; origin: string; address: string; base?: { style?: Record<string, unknown>; [key: string]: unknown }; } /** Copy shifts unanchored coordinates; cutting keeps the formula's referents unchanged. * Names and INDIRECT text remain tokens. Whole bands obey the same mixed-$ rule as cells. */ export const transformSheetFormula = (source: string, dRow: number, dCol: number, mode: "copy" | "cut" = "copy"): string => { if (mode === "cut" || (!dRow && !dCol)) return source; return source.replace( /"(?:[^"]|"")*"|(?<![A-Za-z0-9_.$])((?:(?:'(?:[^']|'')+'|[A-Za-z_][A-Za-z0-9_.]*)!)?)(\$?[A-Za-z]{1,3}\$?[1-9][0-9]*(?![A-Za-z0-9_.]|\s*\()|\$?[A-Za-z]{1,3}:\$?[A-Za-z]{1,3}(?![A-Za-z0-9_.$])|\$?[1-9][0-9]*:\$?[1-9][0-9]*(?![A-Za-z0-9_.$]))/g, (whole, prefix: string | undefined, ref: string | undefined) => { if (!ref) return whole; let valid = true; const shifted = ref.split(":").map((endpoint) => { const cell = /^(\$?)([A-Za-z]+)(\$?)([0-9]+)$/.exec(endpoint); if (cell) { const c = colToIndex(cell[2]) + (cell[1] ? 0 : dCol), r = Number(cell[4]) + (cell[3] ? 0 : dRow); valid &&= c >= 1 && c <= 16384 && r >= 1 && r <= 1048576; return cell[1] + colLetters(c) + cell[3] + r; } const absolute = endpoint.startsWith("$"), raw = endpoint.replace("$", ""), column = /^[A-Za-z]/.test(raw); const value = (column ? colToIndex(raw) : Number(raw)) + (absolute ? 0 : column ? dCol : dRow); valid &&= value >= 1 && value <= (column ? 16384 : 1048576); return (absolute ? "$" : "") + (column ? colLetters(value) : value); }).join(":"); return valid ? (prefix || "") + shifted : ERROR_REF; }); }; const expandFormulaBands = (source: string): string => source.replace( /"(?:[^"]|"")*"|'(?:[^']|'')*'(?!\!)|(?<![A-Za-z0-9_$])((?:(?:'[^']{1,64}'|[A-Za-z_][A-Za-z0-9_]{0,63})!)?)\$?([A-Za-z]{1,3}|[1-9][0-9]{0,6}):\$?([A-Za-z]{1,3}|[1-9][0-9]{0,6})(?![A-Za-z0-9_$!])/g, (whole, prefix: string | undefined, first: string | undefined, last: string | undefined) => { if (!first || !last || /^[A-Za-z]/.test(first) !== /^[A-Za-z]/.test(last)) return whole; return (prefix || "") + (/^[A-Za-z]/.test(first) ? first + "1:" + last + "1048576" : "A" + first + ":XFD" + last); }, ); const boundsOf = (from: string, to: string, own = "", current = ""): FormulaRange => { const a = splitRef(from), b = splitRef(to); const sheet = a.sheet || normalizedSheet(own); if (b.sheet && b.sheet !== sheet) throw new Unsupported("cross-sheet range"); if (Math.min(a.col, b.col, a.row, b.row) < 1 || Math.max(a.col, b.col) > 16384 || Math.max(a.row, b.row) > 1048576) throw new Unsupported("reference bounds"); return [sheet === normalizedSheet(current) ? "" : sheet, Math.min(a.col, b.col), Math.max(a.col, b.col), Math.min(a.row, b.row), Math.max(a.row, b.row)]; }; const freezeSyntax = <T>(value: T): T => { if (value && typeof value === "object" && !Object.isFrozen(value)) { for (const child of Object.values(value)) freezeSyntax(child); Object.freeze(value); } return value; }; const sheetCompileCache = new Map<string, CompiledSheetFormula>(); /** Compile once, evaluate many times. The cache stores text and immutable syntax, never values. * Unsupported evaluation syntax still retains conservative precedents for the Refresh fallback. */ export const compileSheetFormula = ( source: string, ownSheet = "", currentSheet = ownSheet, namedRanges: readonly NamedFormulaRange[] = [], address = "", namePath: readonly string[] = [], budget = { remaining: 200 }, ): CompiledSheetFormula => { const cacheKey = JSON.stringify([source, normalizedSheet(ownSheet), normalizedSheet(currentSheet), namedRanges, address, namePath]); const cached = namePath.length ? undefined : sheetCompileCache.get(cacheKey); if (cached) return cached; const text = expandFormulaBands(source.replace(/"(?:[^"]|"")*"|(?:_xlfn\.|_xlws\.|_xludf\.)+(?=[A-Za-z_][A-Za-z0-9_.]*\s*\()/gi, (token) => token.startsWith('"') ? token : "")); const precedents: FormulaPrecedent[] = [], cells: string[] = [], ranges: FormulaRange[] = []; let syntax: SheetSyntax | null = null, unsupported: string | undefined; try { if (source.length > 500 || namePath.length > 24) throw new Unsupported("formula bounds"); const cursor: Cursor = { tokens: tokenize(text, true), i: 0 }; if (cursor.tokens.length > 200) throw new Unsupported("formula bounds"); const atom = (depth: number): SheetSyntax => { if (--budget.remaining < 0) throw new Unsupported("expanded formula bounds"); const token = take(cursor); if (token.t === "num" || token.t === "str" || token.t === "err") return token; if (token.t === "ref") { if (isOp(peekTok(cursor), ":") && cursor.tokens[cursor.i + 1]?.t === "ref") { take(cursor); const last = rangeEndpoint(take(cursor), "range"); try { return { t: "range", bounds: boundsOf(token.v, last, ownSheet, currentSheet), explicit: true }; } catch { return { t: "err", v: ERROR_REF }; } } const ref = splitRef(token.v); if (ref.col > 16384 || ref.row > 1048576 || ref.row < 1) return { t: "err", v: ERROR_REF }; return { t: "ref", v: formulaReferenceKey(ref.sheet || ownSheet, colLetters(ref.col) + ref.row, currentSheet) }; } if (token.t === "fn") { if (!isOp(peekTok(cursor), "(")) { if (token.v === "TRUE" || token.v === "FALSE") return { t: "boolean", v: token.v }; const bang = token.v.lastIndexOf("!"), name = token.v.slice(bang + 1); const scope = bang < 0 ? ownSheet : token.v.slice(0, bang); const entries = namedRanges.filter((entry) => entry.name.toUpperCase() === name); const target = entries.find((entry) => normalizedSheet(entry.sheet || "") === normalizedSheet(scope)) ?? entries.find((entry) => !entry.sheet); const identity = normalizedSheet(scope) + "!" + name; if (namePath.includes(identity)) throw new FormulaError(CYCLE_REFUSAL); const base = splitRef((target?.base || "A1").replace(/\$/g, "")), caller = splitRef(address || "A1"); const bound = target ? compileSheetFormula(transformSheetFormula(target.ref, caller.row-base.row, caller.col-base.col), target.sheet || ownSheet, currentSheet, namedRanges, address, [...namePath, identity], budget) : null; if (bound?.unsupported) throw new Unsupported(bound.unsupported); return { t: "name", token: token.v, e: bound?.syntax ?? { t: "err", v: ERROR_NAME } }; } take(cursor); const args: SheetSyntax[] = []; if (!isOp(peekTok(cursor), ")")) { while (true) { args.push(isOp(peekTok(cursor), ",") || isOp(peekTok(cursor), ")") ? { t: "num", v: 0 } : expr(1, depth + 1)); if (!isOp(peekTok(cursor), ",")) break; take(cursor); } } if (!isOp(peekTok(cursor), ")")) throw new Unsupported("arguments"); take(cursor); const arity = SUMMARY_FUNCTION_ARITY[token.v]; if (arity && (args.length < arity[0] || args.length > arity[1])) { throw new FormulaError(ERROR_NAME + " This function has the wrong number of arguments."); } return { t: "call", name: token.v, args }; } if (token.t === "op" && token.v === "(") { const inner = expr(0, depth + 1); if (!isOp(peekTok(cursor), ")")) throw new Unsupported("parenthesis"); take(cursor); return inner; } if (token.t === "op" && (token.v === "+" || token.v === "-")) { return { t: "unary", op: token.v, e: expr(6, depth + 1) }; } throw new Unsupported("token"); }; const precedence: Record<string, number> = { ",": 0, "=": 1, "<>": 1, "!=": 1, "<": 1, ">": 1, "<=": 1, ">=": 1, "&": 2, "+": 3, "-": 3, "*": 4, "/": 4, "^": 5, " ": 7, ":": 8 }; const expr = (minimum = 0, depth = 0): SheetSyntax => { if (depth > 24) throw new Unsupported("formula depth"); let node = atom(depth); if (isOp(peekTok(cursor), "#")) { take(cursor); node = { t: "spill", e: node }; } while (true) { const next = peekTok(cursor), op = next?.t === "op" ? next.v : ""; if (!(op in precedence) || precedence[op] < minimum) break; if (--budget.remaining < 0) throw new Unsupported("expanded formula bounds"); take(cursor); node = { t: [",", " ", ":"].includes(op) ? "refop" : "binary", op, l: node, r: expr(precedence[op] + 1, depth + 1) }; } return node; }; if (cursor.tokens.length) syntax = expr(); if (!atEnd(cursor)) throw new Unsupported("trailing tokens"); } catch (err) { syntax = err instanceof FormulaError ? err.message === CYCLE_REFUSAL ? { t: "cycle" } : { t: "err", v: ERROR_NAME } : null; unsupported = err instanceof FormulaError ? undefined : err instanceof Error ? err.message : "syntax"; } let volatile = false; const walk = (node: SheetSyntax): void => { if (node.t === "ref" && !cells.includes(node.v)) { cells.push(node.v); precedents.push({ cell: node.v }); } if (node.t === "range") { ranges.push(node.bounds); precedents.push({ range: node.bounds }); } if (node.t === "call") { volatile ||= ["OFFSET", "INDIRECT", "RAND", "NOW", "TODAY"].includes(node.name); node.args.forEach(walk); } if ("e" in node) walk(node.e); if ("l" in node) { walk(node.l); walk(node.r); } }; if (syntax) walk(syntax); const result = freezeSyntax({ source, syntax, precedents, cells, ranges, unsupported, volatile, ownSheet, currentSheet, address, namedRanges: namedRanges.map((entry) => ({ ...entry })) }); sheetCompileCache.set(cacheKey, result); if (sheetCompileCache.size > 8192) { sheetCompileCache.delete(sheetCompileCache.keys().next().value!); } return result; }; const denseRangeKeys = (bounds: FormulaRange): string[] => { const [sheet, c0, c1, r0, r1] = bounds; if ((c1 - c0 + 1) * (r1 - r0 + 1) > 20000) throw new Unsupported("range needs a sparse cell index"); const keys: string[] = []; for (let row = r0; row <= r1; row += 1) for (let col = c0; col <= c1; col += 1) { const addr = colLetters(col) + row; keys.push(sheet ? `${sheet}!${addr}` : addr); } return keys; }; /** Evaluation walks syntax, never tokenizes or discovers dependencies. Positional lookup ranges * keep their rectangular shape. Aggregates may consume the populated portion of a rectangle. */ const sheetArray = (value: ExpressionValue): SheetArray => { const scalar = expressionCell(value); return scalar.array ?? { values: [scalar], rows: 1, cols: 1 }; }; const arrayResult = (values: CellValue[], rows: number, cols: number): CellValue => { if (!rows || !cols || values.length !== rows*cols || values.length > 5000) { throw new FormulaError(ERROR_VALUE + " This array has an invalid shape."); } const fault = values.find((v) => v.kind === "error"); if (fault?.kind === "error") throw new FormulaError(fault.message, fault); return { ...values[0], array: { values: values.map(({ array: _array, ...cell }) => cell), rows, cols } }; }; const arrayCell = (array: SheetArray, row: number, col: number): CellValue => array.values[row*array.cols+col]; const scalarArgument = (value: ExpressionValue): CellValue => { const array = sheetArray(value); if (array.values.length !== 1) throw new FormulaError(ERROR_VALUE + " This argument needs one value."); const cell = array.values[0]; if (cell.kind === "error") throw new FormulaError(cell.message, cell); return cell; }; /** Server FN_ARITY, including MANY=30. CONCAT is its existing alias. */ const SUMMARY_FUNCTION_ARITY: Readonly<Record<string, readonly [number, number]>> = { SUMPRODUCT: [1, 30], ISTEXT: [1, 1], WEEKNUM: [1, 2], CONCATENATE: [1, 30], CONCAT: [1, 30], YEAR: [1, 1], MONTH: [1, 1], SEARCH: [2, 3], SEARCHB: [2, 3], }; /** SEARCH's wildcard text as a regex source: `?` one character, `*` any run, `~x` a literal x. */ const searchPattern = (text: string): string => { const escape = (ch: string) => ch.replace(/[.*+?^${}()|[\]\\]/g, "\\$&"); let out = ""; for (let i = 0; i < text.length; i += 1) { const ch = text[i]; if (ch === "~" && i + 1 < text.length && "?*~".includes(text[i + 1])) { out += escape(text[i + 1]); i += 1; continue; } out += ch === "*" ? "[\\s\\S]*?" : ch === "?" ? "[\\s\\S]" : escape(ch); } return out; }; const summaryFunction = (name: string, args: ExpressionValue[]): CellValue => { const arrays = args.map(sheetArray); // S67-T24: an IS function never passes an error on; Excel answers FALSE, as the server does. // An error VALUE arrives here (a referenced error cell); a thrown one is caught at the call. if (name === "ISTEXT" && arrays[0]?.values.length === 1 && arrays[0].values[0].kind === "error") { return { kind: "boolean", value: false }; } let pending = false; for (const array of arrays) for (const value of array.values) { if (value.kind === "error") throw new FormulaError(value.message, value); if (value.kind === "unsupported") throw new Unsupported(value.reason); if (value.kind === "pending") throw new PendingInput(name); if ("pending" in value && value.pending) pending = true; } const finish = (value: CellValue): CellValue => pending ? { ...value, pending: true } as CellValue : value; const scalar = (index: number): CellValue => scalarArgument(args[index]); if (name === "SUMPRODUCT") { const width = arrays[0].values.length; if (arrays.some((array) => array.values.length !== width)) throw new FormulaError(ERROR_VALUE); let total = 0; for (let i = 0; i < width; i += 1) { let product = 1; for (const array of arrays) { const value = array.values[i]; // The server's _num treats booleans, blanks and nonnumeric text as zero here. product *= value.kind === "boolean" ? 0 : (functionNumber(value) ?? 0); } total += product; } if (!Number.isFinite(total)) throw new FormulaError(ERROR_VALUE); return finish({ kind: "number", value: total }); } if (name === "ISTEXT") return finish({ kind: "boolean", value: scalar(0).kind === "text" }); if (name === "SEARCH" || name === "SEARCHB") { // S67-T24: Excel's SEARCH, the server's `formula_runtime._call` answer: case-insensitive, // wildcards, #VALUE! when absent. SEARCHB counts bytes only in a double-byte locale. const within = functionText(scalar(1)); let start = 1; if (args.length > 2) { const value = scalar(2); const number = value.kind === "boolean" ? null : functionNumber(value); if (number === null) throw new FormulaError(ERROR_VALUE); start = Math.trunc(number); } if (start < 1 || start > within.length + 1) throw new FormulaError(ERROR_VALUE); const found = new RegExp(searchPattern(functionText(scalar(0))), "i").exec(within.slice(start - 1)); if (!found) throw new FormulaError(ERROR_VALUE); return finish({ kind: "number", value: start + found.index }); } if (name === "CONCATENATE" || name === "CONCAT") { const text = arrays.flatMap((array) => array.values).map((value) => { return value.kind === "boolean" ? (value.value ? "TRUE" : "FALSE") : functionText(value); }).join(""); return finish({ kind: "text", value: text }); } const date = functionDate(scalar(0)); let returnType = 1; if (name === "WEEKNUM" && args.length === 2) { const value = scalar(1); const number = value.kind === "boolean" ? null : functionNumber(value); if (number === null) throw new FormulaError(ERROR_VALUE); returnType = Math.trunc(number); } if (!date) return { kind: "empty" }; if (name === "YEAR") return finish({ kind: "number", value: date.getUTCFullYear() }); if (name === "MONTH") return finish({ kind: "number", value: date.getUTCMonth() + 1 }); if (returnType !== 1 && returnType !== 2) throw new FormulaError(ERROR_VALUE); const first = utcCalendarDate(date.getUTCFullYear(), 1, 1); const offset = returnType === 1 ? first.getUTCDay() : (first.getUTCDay() + 6) % 7; return finish({ kind: "number", value: Math.floor(((+date - +first) / CALENDAR_DAY_MS + offset) / 7) + 1 }); }; const numericArgument = (value: ExpressionValue): number => expressionNumber(scalarArgument(value)).n; // C8 scalar algorithms; numerical root search is local to IRR, never workbook iteration. const EXCEL_EDGE_ARITY: Readonly<Record<string, readonly [number, number]>> = { FV: [3, 5], DATEDIF: [3, 3], IRR: [1, 2], XIRR: [2, 3], "NORM.DIST": [4, 4], BAHTTEXT: [1, 1], ADD: [2, 2], MULTIPLY: [2, 2], }; function excelEdgeFunction(name: string, args: unknown[]): number | string { const bad = (code: string): never => { throw new Error(code); }; const arity = EXCEL_EDGE_ARITY[name]; if (!arity || args.length < arity[0] || args.length > arity[1]) return bad("#VALUE!"); const num = (v: unknown): number => { if (v === null || v === undefined || v === "") return 0; if (!["number", "string", "boolean"].includes(typeof v)) return bad("#VALUE!"); const n = Number(v); return Number.isFinite(n) ? n : bad("#VALUE!"); }; const date = (v: unknown): Date => { const text = String(v).slice(0, 10), d = new Date(text+"T00:00:00Z"); if (!/^\d{4}-\d{2}-\d{2}$/.test(text) || !Number.isFinite(+d) || d.toISOString().slice(0, 10) !== text) return bad("#VALUE!"); return d; }; if (name === "IRR" || name === "XIRR") { const raw = Array.isArray(args[0]) ? args[0] : [args[0]]; const values = raw.filter((v): v is number => typeof v === "number" && Number.isFinite(v)); let times = values.map((_, i) => i); if (name === "XIRR") { const dates = (Array.isArray(args[1]) ? args[1] : [args[1]]).map(date); if (dates.length !== values.length) return bad("#NUM!"); times = dates.map((d) => (+d-+dates[0])/86400000/365); } let rate = args.length > (name === "IRR" ? 1 : 2) ? num(args[name === "IRR" ? 1 : 2]) : .1; if (values.length < 2 || !values.some((v) => v < 0) || !values.some((v) => v > 0) || rate <= -1 || times.some((v) => v < 0)) return bad("#NUM!"); const scale = Math.max(...values.map(Math.abs)); for (let i = 0; i < 100; i++) { const discounts = times.map((t) => Math.exp(-t*Math.log1p(rate))); const value = values.reduce((sum, v, j) => sum+v/scale*discounts[j], 0); const derivative = values.reduce((sum, v, j) => sum-times[j]*v/scale*discounts[j]/(1+rate), 0); if (Math.abs(value) < 1e-13) return rate; if (!derivative || !Number.isFinite(derivative)) break; let next = rate-value/derivative; if (next <= -1) next = (rate-1)/2; if (!Number.isFinite(next)) break; if (Math.abs(next-rate) < 1e-12 && Math.abs(value) < 1e-9) return next; rate = next; } return bad("#NUM!"); } if (name === "DATEDIF") { const start = date(args[0]), end = date(args[1]), unit = String(args[2]).toUpperCase(); if (+start > +end || !["Y","M","D","YM","YD","MD"].includes(unit)) return bad("#NUM!"); const months = (end.getUTCFullYear()-start.getUTCFullYear())*12+end.getUTCMonth()-start.getUTCMonth()-Number(end.getUTCDate() < start.getUTCDate()); if (unit === "Y") return Math.floor(months/12); if (unit === "M") return months; if (unit === "YM") return months%12; if (unit === "D") return (+end-+start)/86400000; if (unit === "MD") return end.getUTCDate()-start.getUTCDate()+(end.getUTCDate() < start.getUTCDate() ? new Date(Date.UTC(end.getUTCFullYear(), end.getUTCMonth(), 0)).getUTCDate() : 0); const year = end.getUTCFullYear()-Number(end.getUTCMonth() < start.getUTCMonth() || (end.getUTCMonth() === start.getUTCMonth() && end.getUTCDate() < start.getUTCDate())); const anniversary = new Date(0); anniversary.setUTCFullYear(year, start.getUTCMonth(), start.getUTCDate()); return (+end-+anniversary)/86400000; } const n = args.map(num); if (name === "ADD") return n[0]+n[1]; if (name === "MULTIPLY") return n[0]*n[1]; if (name === "FV") { const [rate, periods, payment, pv = 0, when = 0] = n; if (![0,1].includes(when)) return bad("#VALUE!"); const value = rate === 0 ? -(pv+payment*periods) : -(pv*(1+rate)**periods+payment*(1+rate*when)*((1+rate)**periods-1)/rate); return Number.isFinite(value) ? value : bad("#NUM!"); } if (name === "NORM.DIST") { const [x, mean, sigma] = n; if (sigma <= 0) return bad("#NUM!"); const z = (x-mean)/sigma; if (!n[3]) return Math.exp(-z*z/2)/(sigma*Math.sqrt(2*Math.PI)); // Integrate the Gaussian through its positive-term series. Stable to double precision // within ten standard deviations; outside that interval the rounded tail is 0 or 1. if (z <= -10) return 0; if (z >= 10) return 1; let term = z, sum = z; for (let k = 1; k < 300; k++) { term *= z*z/(2*k+1); sum += term; if (Math.abs(term) < Math.abs(sum)*1e-16) break; } return Math.max(0, Math.min(1, .5+sum*Math.exp(-z*z/2)/Math.sqrt(2*Math.PI))); } const words = ["ΰΈ¨ΰΈΉΰΈ™ΰΈ’ΰΉŒ","ΰΈ«ΰΈ™ΰΈΆΰΉˆΰΈ‡","ΰΈͺΰΈ­ΰΈ‡","ΰΈͺΰΈ²ΰΈ‘","ΰΈͺม่","ΰΈ«ΰΉ‰ΰΈ²","หก","ΰΉ€ΰΈˆΰΉ‡ΰΈ”","แปด","เก้า"]; const places = ["","ΰΈͺิบ","ΰΈ£ΰΉ‰ΰΈ­ΰΈ’","ΰΈžΰΈ±ΰΈ™","ΰΈ«ΰΈ‘ΰΈ·ΰΉˆΰΈ™","แΰΈͺΰΈ™"]; const spell = (integer: number): string => { if (integer >= 1000000) return spell(Math.floor(integer/1000000))+"ΰΈ₯ΰΉ‰ΰΈ²ΰΈ™"+(integer%1000000 ? spell(integer%1000000) : ""); let text = ""; for (let p = 5; p >= 0; p--) { const digit = Math.floor(integer/10**p)%10; if (!digit) continue; text += (p === 1 && digit === 2 ? "ฒม่" : p === 1 && digit === 1 ? "" : p === 0 && digit === 1 && integer > 10 ? "ΰΉ€ΰΈ­ΰΉ‡ΰΈ”" : words[digit])+places[p]; } return text || words[0]; }; const cents = Math.round(Number((Math.abs(n[0])*100).toPrecision(15))), fraction = cents%100; return (n[0] < 0 ? "ΰΈ₯บ" : "")+spell(Math.floor(cents/100))+"ΰΈšΰΈ²ΰΈ—"+(fraction ? spell(fraction)+"ΰΈͺΰΈ•ΰΈ²ΰΈ‡ΰΈ„ΰΉŒ" : "ΰΈ–ΰΉ‰ΰΈ§ΰΈ™"); } const logicalCell = (value: CellValue): boolean => { if (value.kind === "error") throw new FormulaError(value.message, value); if (value.kind === "unsupported") throw new Unsupported(value.reason); if (value.kind === "empty" || value.kind === "pending") return false; if (value.kind === "text") { if (!["TRUE", "FALSE"].includes(value.value.toUpperCase())) throw new FormulaError(ERROR_VALUE); return value.value.toUpperCase() === "TRUE"; } return Boolean(value.value); }; const excelComparison = (a: unknown, b: unknown, op: string): boolean => { const rank = (v: unknown) => typeof v === "boolean" ? 2 : typeof v === "string" ? 1 : 0; const normalize = (v: unknown): string | number => typeof v === "string" ? v.toLowerCase() : typeof v === "boolean" ? Number(v) : Number(Number(v ?? 0).toPrecision(15)); const x = normalize(a), y = normalize(b); const order = rank(a) !== rank(b) ? rank(a)-rank(b) : x === y ? 0 : x < y ? -1 : 1; return op === "=" ? order === 0 : op === "!=" || op === "<>" ? order !== 0 : op === "<" ? order < 0 : op === ">" ? order > 0 : op === "<=" ? order <= 0 : order >= 0; }; const arrayBinary = (left: ExpressionValue, right: ExpressionValue, op: string): CellValue => { const a = sheetArray(left), b = sheetArray(right); const rows = Math.max(a.rows, b.rows), cols = Math.max(a.cols, b.cols); if ([a, b].some((v) => ![1, rows].includes(v.rows) || ![1, cols].includes(v.cols))) { throw new FormulaError(ERROR_VALUE + " These arrays have incompatible shapes."); } const values: CellValue[] = []; for (let row = 0; row < rows; row++) for (let col = 0; col < cols; col++) { const x = arrayCell(a, row%a.rows, col%a.cols), y = arrayCell(b, row%b.rows, col%b.cols); if (x.kind === "error") throw new FormulaError(x.message, x); if (y.kind === "error") throw new FormulaError(y.message, y); const l = "value" in x ? x.value : "", r = "value" in y ? y.value : ""; if (op === "&") { values.push({ kind: "text", value: String(l)+String(r) }); continue; } if (["=", "!=", "<>", "<", ">", "<=", ">="].includes(op)) { values.push({ kind: "boolean", value: excelComparison(l, r, op) }); continue; } const numeric = (cell: CellValue): number => cell.kind === "text" && ["TRUE", "FALSE"].includes(cell.value) ? Number(cell.value === "TRUE") : expressionNumber(cell).n; const u = numeric(x), v = numeric(y); if (op === "/" && v === 0) throw new FormulaError(ERROR_DIV0 + " Division by zero in an array."); const value = op === "+" ? u+v : op === "-" ? u-v : op === "*" ? u*v : op === "^" ? u**v : u/v; values.push({ kind: "number", value: cleanNumber(value) }); } return arrayResult(values, rows, cols); }; // English is explicit and independent of the browser or server's host locale. Canonical // composed/decomposed accents compare equal, so stable sort preserves their authored order. const englishArrayCollator = new Intl.Collator("en", { sensitivity: "accent" }); const compareArrayCells = (a: CellValue, b: CellValue): number => { if (a.kind === "error") throw new FormulaError(a.message, a); if (b.kind === "error") throw new FormulaError(b.message, b); const rank = (v: CellValue) => v.kind === "number" ? 0 : v.kind === "text" ? 1 : 3; if (rank(a) !== rank(b)) return rank(a)-rank(b); return a.kind === "number" && b.kind === "number" ? a.value-b.value : englishArrayCollator.compare(a.kind === "text" ? a.value : "", b.kind === "text" ? b.value : ""); }; const arrayFunctions = (name: string, args: ExpressionValue[]): CellValue => { const invalid = (): never => { throw new FormulaError(ERROR_VALUE + " Invalid array arguments."); }; const range = (i: number) => sheetArray(args[i]); if (name === "VLOOKUP" || name === "HLOOKUP") { if (args.length < 3 || args.length > 4) return invalid(); const target = scalarArgument(args[0]), table = range(1), offset = numericArgument(args[2]); const vertical = name === "VLOOKUP", span = vertical ? table.rows : table.cols; if (!Number.isInteger(offset) || offset < 1 || offset > (vertical ? table.cols : table.rows)) return invalid(); const approximate = args.length < 4 || logicalCell(scalarArgument(args[3])); let hit = -1; for (let i = 0; i < span; i++) { const cell = arrayCell(table, vertical ? i : 0, vertical ? 0 : i); const order = compareArrayCells(cell, target); if (approximate) { if (order > 0) break; hit = i; } else if (order === 0) { hit = i; break; } } if (hit < 0) throw new FormulaError("#N/A"); return arrayCell(table, vertical ? hit : offset-1, vertical ? offset-1 : hit); } if (name === "FILTER") { if (args.length < 2 || args.length > 3) return invalid(); const array = range(0), include = range(1), vertical = include.cols === 1 && include.rows === array.rows; if (!vertical && !(include.rows === 1 && include.cols === array.cols)) return invalid(); const selected = include.values.flatMap((cell, index) => { if (cell.kind === "error") throw new FormulaError(cell.message, cell); if (cell.kind === "text" && !["TRUE", "FALSE"].includes(cell.value)) return invalid(); return cell.kind === "boolean" ? cell.value ? [index] : [] : cell.kind === "number" ? cell.value !== 0 ? [index] : [] : cell.kind === "text" && cell.value === "TRUE" ? [index] : []; }); if (!selected.length) { if (args.length === 3) { const fallback = sheetArray(args[2]); return arrayResult(fallback.values, fallback.rows, fallback.cols); } throw new FormulaError("#CALC! This filter has no matching values."); } const rows = vertical ? selected : Array.from({ length: array.rows }, (_, i) => i); const cols = vertical ? Array.from({ length: array.cols }, (_, i) => i) : selected; return arrayResult(rows.flatMap((r) => cols.map((c) => arrayCell(array, r, c))), rows.length, cols.length); } if (name === "SORT") { if (!args.length || args.length > 4) return invalid(); const array = range(0), index = args.length > 1 ? numericArgument(args[1]) : 1; const order = args.length > 2 ? numericArgument(args[2]) : 1; const flag: CellValue = args.length > 3 ? scalarArgument(args[3]) : { kind: "boolean", value: false }; if (flag.kind !== "boolean" && (flag.kind !== "text" || !["TRUE", "FALSE"].includes(flag.value))) return invalid(); const byCol = flag.value === true || flag.value === "TRUE"; if (!Number.isInteger(index) || index < 1 || index > (byCol ? array.rows : array.cols) || ![-1, 1].includes(order)) return invalid(); const indices = Array.from({ length: byCol ? array.cols : array.rows }, (_, i) => i) .sort((a, b) => order*compareArrayCells(arrayCell(array, byCol ? index-1 : a, byCol ? a : index-1), arrayCell(array, byCol ? index-1 : b, byCol ? b : index-1))); return arrayResult(Array.from({ length: array.rows }, (_, r) => Array.from({ length: array.cols }, (_, c) => arrayCell(array, byCol ? r : indices[r], byCol ? indices[c] : c))).flat(), array.rows, array.cols); } if (name === "INDEX") { if (args.length < 2 || args.length > 3) return invalid(); const array = range(0), first = Math.trunc(numericArgument(args[1])); const row = args.length === 2 && array.rows === 1 ? 1 : first; const col = args.length > 2 ? Math.trunc(numericArgument(args[2])) : array.rows === 1 ? first : array.cols === 1 ? 1 : 0; if (row < 0 || row > array.rows || col < 0 || col > array.cols) return invalid(); if (row && col) return arrayCell(array, row-1, col-1); const rows = row ? [row-1] : Array.from({ length: array.rows }, (_, i) => i); const cols = col ? [col-1] : Array.from({ length: array.cols }, (_, i) => i); return arrayResult(rows.flatMap((r) => cols.map((c) => arrayCell(array, r, c))), rows.length, cols.length); } if (name === "XLOOKUP") { if (args.length < 3 || args.length > 5) return invalid(); const lookup = range(1), result = range(2), vertical = lookup.cols === 1; if ((!vertical && lookup.rows !== 1) || (vertical ? lookup.rows !== result.rows : lookup.cols !== result.cols)) return invalid(); const wanted = range(0), mode = args.length > 4 ? numericArgument(args[4]) : 0; if (![-1, 0, 1, 2].includes(mode)) return invalid(); const one = (needle: CellValue): CellValue => { const raw = (cell: CellValue) => "value" in cell ? String(cell.value).toLowerCase() : ""; let hit = lookup.values.findIndex((cell) => mode === 2 ? criteriaMatches(cell, { kind: "text", value: raw(needle) }) : cell.kind === needle.kind && raw(cell) === raw(needle)); if (hit < 0 && (mode === -1 || mode === 1)) lookup.values.forEach((cell, i) => { const direction = compareArrayCells(cell, needle); if ((mode === -1 ? direction < 0 : direction > 0) && (hit < 0 || (mode === -1 ? compareArrayCells(cell, lookup.values[hit]) > 0 : compareArrayCells(cell, lookup.values[hit]) < 0))) hit = i; }); if (hit < 0) { if (args.length > 3) return expressionCell(args[3]); throw new FormulaError("#N/A This lookup found nothing to match."); } const values = Array.from({ length: vertical ? result.cols : result.rows }, (_, i) => arrayCell(result, vertical ? hit : i, vertical ? i : hit)); return values.length === 1 ? values[0] : arrayResult(values, vertical ? 1 : result.rows, vertical ? result.cols : 1); }; if (wanted.values.length === 1) return one(wanted.values[0]); const values = wanted.values.map(one); if (values.some((v) => v.array && v.array.values.length > 1)) return invalid(); return arrayResult(values, wanted.rows, wanted.cols); } if (name === "SUMIFS" || name === "SUMIF" || name === "COUNTIF" || name === "COUNTIFS") { const multi = name.endsWith("IFS"), count = name.startsWith("COUNT"); if ((multi && (args.length < (count ? 2 : 3) || args.length%2 !== (count ? 0 : 1))) || (!multi && (args.length < 2 || args.length > (count ? 2 : 3)))) return invalid(); const target = count ? range(0) : multi ? range(0) : args.length > 2 ? range(2) : range(0); const pairs = multi ? Array.from({ length: (args.length-(count ? 0 : 1))/2 }, (_, i) => (count ? 0 : 1)+i*2) : [0]; if (pairs.some((i) => range(i).rows !== target.rows || range(i).cols !== target.cols)) return invalid(); let total = 0; target.values.forEach((cell, index) => { if (pairs.every((i) => criteriaMatches(range(i).values[index], scalarArgument(args[i+1])))) { if (cell.kind === "error") throw new FormulaError(cell.message, cell); total += count ? 1 : cell.kind === "number" ? cell.value : 0; } }); return { kind: "number", value: cleanNumber(total) }; } return invalid(); }; const criteriaMatches = (cell: CellValue, criterion: CellValue): boolean => { if (cell.kind === "error") throw new FormulaError(cell.message, cell); if (criterion.kind === "error") throw new FormulaError(criterion.message, criterion); const value = "value" in cell ? cell.value : ""; const raw = "value" in criterion ? criterion.value : ""; const match = /^(<=|>=|<>|=|<|>)(.*)$/.exec(String(raw)); const op = match?.[1] || "=", expected = match ? match[2] : raw; if (!match && typeof raw === "string" && /[*?~]/.test(raw)) { let pattern = ""; for (let i = 0; i < raw.length; i++) { let ch = raw[i], literal = false; if (ch === "~" && i+1 < raw.length) { ch = raw[++i]; literal = true; } pattern += !literal && ch === "*" ? ".*" : !literal && ch === "?" ? "." : ch.replace(/[.*+?^${}()|[\]\\]/g, "\\$&"); } return new RegExp("^"+pattern+"$", "i").test(String(value)); } const numeric = expected !== "" && Number.isFinite(Number(expected)); const a = numeric ? Number(value) : String(value).toLowerCase(), b = numeric ? Number(expected) : String(expected).toLowerCase(); return op === "=" ? a === b : op === "<>" ? a !== b : op === "<" ? a < b : op === ">" ? a > b : op === "<=" ? a <= b : a >= b; }; const inferredFormat = (node: SheetSyntax, candidates: unknown[]): unknown => { const formats = candidates.filter(Boolean), kind = (f: unknown) => (f as { kind?: string })?.kind; if (node.t === "binary" && ["=", "!=", "<>", "<", ">", "<=", ">=", "&", "^"].includes(node.op)) return undefined; if (node.t === "call") { if (["DATE", "DATEVALUE", "TODAY", "NOW", "EDATE", "EOMONTH", "WORKDAY", "DATEADD"].includes(node.name)) return { kind: "date" }; if (["IRR", "XIRR"].includes(node.name)) return { kind: "percent", decimals: 2 }; if (!["IF", "IFS", "IFERROR", "IFNA", "SWITCH", "CHOOSE", "SUM", "AVERAGE", "MIN", "MAX", "SUMIF", "SUMIFS", "AVERAGEIF", "AVERAGEIFS", "INDEX", "XLOOKUP", "VLOOKUP", "HLOOKUP", "OFFSET", "INDIRECT", "FILTER", "SORT", "ROUND", "ROUNDUP", "ROUNDDOWN", "ABS", "ADD", "MULTIPLY", "FV"].includes(node.name)) return undefined; } if (node.t === "binary" && formats.length > 1) { if (node.op === "-" && kind(formats[0]) === "date" && kind(formats[1]) === "date") return undefined; if (node.op === "/" && kind(formats[0]) === kind(formats[1])) return { kind: "percent", decimals: 2 }; } return formats.find((f) => ["currency", "date"].includes(kind(f) ?? "")) ?? formats[0]; }; export const evaluateCompiledSheetFormula = ( compiled: CompiledSheetFormula, inputGet: Getter, rangeKeys: (range: FormulaRange) => string[] = denseRangeKeys, context: { now?: number; dateSystem?: "1900" | "1904"; address?: string; volatileValues?: Map<SheetSyntax, number>; sheets?: readonly string[]; spillRange?: (origin: string) => FormulaRange | undefined } = {}, ): CellValue => { if (compiled.unsupported) return { kind: "unsupported", reason: compiled.unsupported }; const formatFrames: unknown[][] = []; const get: Getter = (key) => { const result = inputGet(key); formatFrames[formatFrames.length-1]?.push(result.style?.format); return result; }; get.workbookReferences = inputGet.workbookReferences; const volatileValues = context.volatileValues ?? new Map<SheetSyntax, number>(); const isReference = (node: SheetSyntax): boolean => node.t === "name" ? isReference(node.e) : node.t === "ref" || node.t === "range" || node.t === "refop" || node.t === "spill" || (node.t === "call" && ["OFFSET", "INDIRECT"].includes(node.name)); const checked = (refs: FormulaRange[]): FormulaRange[] => { for (const [s, c0, c1, r0, r1] of refs) { if (Math.min(c0, r0) < 1 || c1 > 16384 || r1 > 1048576 || c0 > c1 || r0 > r1) { throw new FormulaError(ERROR_REF + " This reference is outside the worksheet."); } const sheet = s || normalizedSheet(compiled.currentSheet); if (context.sheets && !context.sheets.some((name) => normalizedSheet(name) === sheet)) { throw new FormulaError(ERROR_REF + " This tab is not in the workbook."); } } return refs; }; const areas = (node: SheetSyntax): FormulaRange[] => { if (node.t === "name") return areas(node.e); if (node.t === "spill") { if (node.e.t !== "ref") throw new FormulaError(ERROR_REF + " A spill reference needs an origin."); const origin = get(node.e.v); if (origin.kind === "error") throw new FormulaError(origin.message, origin); const region = context.spillRange?.(node.e.v); if (!region) throw new FormulaError(ERROR_REF + " This cell has no spilled array."); return [region]; } if (node.t === "err") throw new FormulaError(node.v); if (node.t === "cycle") throw new FormulaError(CYCLE_REFUSAL); if (node.t === "ref") { const ref = splitRef(node.v); return checked([[ref.sheet, ref.col, ref.col, ref.row, ref.row]]); } if (node.t === "range") return checked([node.bounds]); if (node.t === "refop") { const left = areas(node.l), right = areas(node.r); if (node.op === ",") return [...left, ...right]; if (node.op === ":") { if (left.length !== 1 || right.length !== 1 || left[0][0] !== right[0][0]) { throw new FormulaError(ERROR_REF + " A range must stay on one worksheet."); } const a = left[0], b = right[0]; return checked([[a[0], Math.min(a[1], b[1]), Math.max(a[2], b[2]), Math.min(a[3], b[3]), Math.max(a[4], b[4])]]); } const intersections: FormulaRange[] = []; for (const a of left) for (const b of right) { const range: FormulaRange = [a[0], Math.max(a[1], b[1]), Math.min(a[2], b[2]), Math.max(a[3], b[3]), Math.min(a[4], b[4])]; if (a[0] === b[0] && range[1] <= range[2] && range[3] <= range[4]) intersections.push(range); } if (!intersections.length) throw new FormulaError("#NULL! These references do not intersect."); return intersections; } if (node.t === "call" && node.name === "OFFSET") { if (node.args.length < 3 || node.args.length > 5) throw new FormulaError(ERROR_VALUE + " OFFSET arguments are invalid."); const base = areas(node.args[0]); if (base.length !== 1) throw new FormulaError(ERROR_VALUE + " OFFSET needs one area."); const offsets = node.args.slice(1).map((arg) => Math.trunc(expressionNumber(evaluate(arg)).n)); const [s, c0, c1, r0, r1] = base[0], [dr, dc] = offsets; return checked([[s, c0+dc, c0+dc+(offsets[3] ?? c1-c0+1)-1, r0+dr, r0+dr+(offsets[2] ?? r1-r0+1)-1]]); } if (node.t === "call" && node.name === "INDIRECT") { if (!node.args.length || node.args.length > 2) throw new FormulaError(ERROR_VALUE + " INDIRECT arguments are invalid."); const value = expressionCell(evaluate(node.args[0])); if (value.kind !== "text") throw new FormulaError(ERROR_REF + " INDIRECT expects reference text."); let text = value.value; const a1 = node.args[1] === undefined || !(node.args[1].t === "boolean" && node.args[1].v === "FALSE"); if (!a1) { const match = /^R(\[-?\d+\]|\d*)C(\[-?\d+\]|\d*)$/i.exec(text); if (!match || !context.address) throw new FormulaError(ERROR_REF + " Invalid R1C1 reference."); const here = splitRef(context.address); const coordinate = (part: string, origin: number) => part.startsWith("[") ? origin+Number(part.slice(1, -1)) : part ? Number(part) : origin; const r = coordinate(match[1], here.row), c = coordinate(match[2], here.col); checked([[here.sheet, c, c, r, r]]); text = colLetters(c)+r; } if (/[\[\]]/.test(text)) throw new FormulaError(ERROR_REF + " External workbook references are not supported."); const target = compileSheetFormula(text, compiled.ownSheet, compiled.currentSheet, compiled.namedRanges, compiled.namedRanges.length ? context.address || compiled.address : ""); if (!target.syntax || target.unsupported || target.syntax.t === "call" || (!isReference(target.syntax) && target.syntax.t !== "err")) { throw new FormulaError(ERROR_REF + " Invalid reference text."); } return areas(target.syntax); } throw new FormulaError(ERROR_REF + " This expression is not a reference."); }; const evaluate = (node: SheetSyntax): ExpressionValue => { formatFrames.push([]); let result: CellValue; try { result = expressionCell(evaluateValue(node)); const format = result.style?.format ?? inferredFormat(node, formatFrames[formatFrames.length-1]); if (result.kind === "number") result = { ...result, value: Number(result.value.toPrecision(15)) }; const style = { ...result.style }; delete style.format; if (format && (result.kind === "number" || result.array || (result.kind === "text" && (format as { kind?: string }).kind === "date"))) style.format = format; result = { ...result, style }; } finally { formatFrames.pop(); } formatFrames[formatFrames.length-1]?.push(result.style?.format); return result; }; const evaluateValue = (node: SheetSyntax): ExpressionValue => { if (node.t === "num") return { n: node.v, pending: false }; if (node.t === "str") return { kind: "text", value: node.v }; if (node.t === "ref") { areas(node); return get(node.v); } if (node.t === "err") throw new FormulaError(node.v); if (node.t === "cycle") throw new FormulaError(CYCLE_REFUSAL); if (node.t === "boolean") return { kind: "boolean", value: node.v === "TRUE" }; if (node.t === "name" && !isReference(node.e)) return evaluate(node.e); if (isReference(node)) { const refs = areas(node); if (refs.length === 1 && refs[0][1] === refs[0][2] && refs[0][3] === refs[0][4]) return get(denseRangeKeys(refs[0])[0]); if (refs.length !== 1) throw new FormulaError(ERROR_VALUE + " An array needs one area."); const bounds = refs[0]; return arrayResult(denseRangeKeys(bounds).map(get), bounds[4]-bounds[3]+1, bounds[2]-bounds[1]+1); } if (node.t === "unary") { const value = evaluate(node.e); if ("kind" in value && value.array) return arrayBinary(value, { n: node.op === "-" ? -1 : 1, pending: false }, "*"); const blank = arithmeticBlank(value); if (blank) return blank; const inner = expressionNumber(value); return { n: node.op === "-" ? -inner.n : inner.n, pending: inner.pending }; } if (node.t === "binary") { const first = evaluate(node.l), second = evaluate(node.r); if (("kind" in first && first.array) || ("kind" in second && second.array)) return arrayBinary(first, second, node.op); if (node.op === "&" || ["=", "!=", "<>", "<", ">", "<=", ">="].includes(node.op)) { const l = expressionCell(first), r = expressionCell(second); if (l.kind === "error") throw new FormulaError(l.message, l); if (r.kind === "error") throw new FormulaError(r.message, r); const a = "value" in l ? l.value : "", b = "value" in r ? r.value : ""; if (node.op === "&") return { kind: "text", value: String(a) + String(b) }; return { kind: "boolean", value: excelComparison(a, b, node.op) }; } const blank = arithmeticBlank(first, second); if (blank) return blank; if (node.op === "+" || node.op === "-") { const dated = isoDayArithmetic(expressionCell(first), expressionCell(second), node.op); // ⚠ A DAY COUNT IS A PLAIN NUMBER. Left unformatted, `inferredFormat` reads the `DATE()` // operand's date format and a 17 day gap painted as "Jan 1, 1970". `=TODAY()-7` is ISO // text minus days, so it returns ISO text below and keeps its inferred date. if (dated && !("kind" in dated)) { return { kind: "number", value: dated.n, ...(dated.pending ? { pending: true } : {}), style: { format: { kind: "general" } } }; } if (dated) return dated; } const left = expressionNumber(first), right = expressionNumber(second); if (node.op === "/" && right.n === 0) { if (right.pending) throw new PendingInput("divisor"); throw new FormulaError(ERROR_DIV0 + " This formula divides by zero."); } return { n: node.op === "+" ? left.n + right.n : node.op === "-" ? left.n - right.n : node.op === "*" ? left.n * right.n : node.op === "^" ? left.n ** right.n : left.n / right.n, pending: left.pending || right.pending }; } if (node.t !== "call") throw new FormulaError(ERROR_VALUE + " Invalid expression."); const summaryArity = SUMMARY_FUNCTION_ARITY[node.name]; if (summaryArity) { if (node.args.length < summaryArity[0] || node.args.length > summaryArity[1]) { throw new FormulaError(ERROR_NAME + " This function has the wrong number of arguments."); } if (node.name === "ISTEXT") { // S67-T24: an IS function never passes an error on (Excel, and the server, answer FALSE). // A pending or unsupported argument still propagates: only a FormulaError is contained. try { return summaryFunction(node.name, node.args.map(evaluate)); } catch (error) { if (error instanceof FormulaError) return { kind: "boolean", value: false }; throw error; } } return summaryFunction(node.name, node.args.map(evaluate)); } if (EXCEL_EDGE_ARITY[node.name]) { const args = node.args.map((arg, i) => { const value = evaluate(arg), array = sheetArray(value); const raw = (cell: CellValue): unknown => { if (cell.kind === "error") throw new FormulaError(cell.message, cell); if (cell.kind === "unsupported") throw new Unsupported(cell.reason); return "value" in cell ? cell.value : null; }; return (node.name === "IRR" && i === 0) || (node.name === "XIRR" && i < 2) ? array.values.map(raw) : raw(scalarArgument(value)); }); try { const value = excelEdgeFunction(node.name, args); return typeof value === "number" ? { kind: "number", value } : { kind: "text", value }; } catch (error) { throw new FormulaError(error instanceof Error ? error.message : ERROR_VALUE); } } if (node.name === "DATE") { if (node.args.length !== 3) throw new FormulaError(ERROR_VALUE); const [year, month, day] = node.args.map((arg) => numericArgument(evaluate(arg))); const date = new Date(0); date.setUTCFullYear(Math.trunc(year), Math.trunc(month)-1, Math.trunc(day)); if (!Number.isFinite(+date)) throw new FormulaError("#NUM!"); return { kind: "text", value: date.toISOString().slice(0, 10) }; } if (["NOW", "TODAY", "RAND"].includes(node.name)) { if (node.args.length) throw new FormulaError(ERROR_VALUE + " This function takes no arguments."); const epoch = context.dateSystem === "1904" ? Date.UTC(1904, 0, 1) : Date.UTC(1899, 11, 30); if (!volatileValues.has(node)) { const serial = ((context.now ?? Date.now()) - epoch) / 86400000; volatileValues.set(node, node.name === "RAND" ? Math.random() : node.name === "TODAY" ? Math.floor(serial) : serial); } // ⭐⭐ HOTFIX 2026-09-28: TODAY() IS ISO TEXT, as `formula_runtime.py::volatile_value` answers // it. As a serial, Daily Sales R7 (=E14, E12 =TODAY()) fed `YEAR(R7)` a number `functionDate` // cannot read: rows 2, 3 and 5 painted blank and `=(DATE(YEAR(R7),12,31) - R7)/7` painted // #VALUE! over the 13.43 the server stores. The serial stays the pass's one clock. if (node.name === "TODAY") { return { kind: "text", value: new Date(epoch + volatileValues.get(node)! * 86400000).toISOString().slice(0, 10) }; } if (node.name === "NOW") { // Round the serial conversion to milliseconds before Date truncates fractional drift. const instant = new Date(Math.round(epoch + volatileValues.get(node)! * 86400000)); return { kind: "text", value: instant.toISOString().slice(0, 19).replace("T", " ") }; } return { n: volatileValues.get(node)!, pending: false }; } if (node.name === "IF") { if (node.args.length < 2 || node.args.length > 3) throw new FormulaError(ERROR_VALUE); const condition = expressionCell(evaluate(node.args[0])); const truth = logicalCell(condition); return node.args[truth ? 1 : 2] ? evaluate(node.args[truth ? 1 : 2]) : { kind: "empty" }; } if (node.name === "IFERROR" || node.name === "IFNA") { if (node.args.length !== 2) throw new FormulaError(ERROR_VALUE + " IFERROR needs two arguments."); const blank = (value: ExpressionValue): ExpressionValue => "kind" in value && value.kind === "empty" ? { kind: "text", value: "" } : value; try { const value = evaluate(node.args[0]), scalar = expressionCell(value); if (scalar.kind === "error") { if (node.name === "IFNA" && scalar.code !== "#N/A") return scalar; return blank(evaluate(node.args[1])); } return blank(value); } catch (error) { if (error instanceof PendingReference || error instanceof Unsupported || error instanceof PendingInput) throw error; if (node.name === "IFNA" && (!(error instanceof FormulaError) || errorValue(error.message).kind !== "error" || !(error.source?.code === "#N/A" || error.message.startsWith("#N/A")))) throw error; return blank(evaluate(node.args[1])); } } if (["FILTER", "SORT", "XLOOKUP", "INDEX", "VLOOKUP", "HLOOKUP", "SUMIFS", "SUMIF", "COUNTIF", "COUNTIFS"].includes(node.name)) { return arrayFunctions(node.name, node.args.map(evaluate)); } if (AGGREGATE_SET.has(node.name) || node.name === "AVERAGE") { if (!node.args.length) throw new FormulaError(ERROR_VALUE + " An aggregate needs an argument."); const addrs: string[] = [], literals = new Map<string, CellValue>(); node.args.forEach((arg, index) => { if (isReference(arg)) addrs.push(...areas(arg).flatMap((range) => rangeKeys(range))); else { const values = sheetArray(evaluate(arg)).values; values.forEach((value, offset) => { const key = "!argument"+index+":"+offset; addrs.push(key); literals.set(key, value); }); } }); return evalAggregate(node.name === "AVERAGE" ? "AVG" : node.name, addrs, (key) => literals.get(key) ?? get(key)); } if (!Object.prototype.hasOwnProperty.call(CALL_ARITY, node.name)) throw new Unsupported(node.name); return parseLookup(node.name, { tokens: [], i: 0 }, get, () => node.args.map((arg): LookupArgument => { if (arg.t === "boolean") return { kind: "logical", literal: arg.v as "TRUE" | "FALSE" }; if (isReference(arg)) { const refs = areas(arg); if (refs.length !== 1) throw new FormulaError(ERROR_VALUE + " This function needs one area."); const bounds = refs[0]; return { kind: "range", addrs: denseRangeKeys(bounds), explicit: arg.t !== "ref", rows: bounds[4] - bounds[3] + 1, cols: bounds[2] - bounds[1] + 1 }; } return expressionCell(evaluate(arg)); })); }; try { if (!compiled.syntax) return { kind: "empty" }; const result = evaluate(compiled.syntax); if ("kind" in result) return result; if (!Number.isFinite(result.n)) return { kind: "error", message: "This formula could not be calculated." }; const value = cleanNumber(result.n); return result.pending ? { kind: "number", value, pending: true } : { kind: "number", value }; } catch (err) { if (err instanceof PendingReference) throw err; if (err instanceof PendingInput) return { kind: "pending" }; if (err instanceof Unsupported) { const reason = err.message; return { kind: "unsupported", reason }; } // ⭐⭐ W61-T05 / THE COMBINE β€” AN ERROR LEAVES BY THIS DOOR WITH ITS CODE TOO. // `err.source` is the ORIGINAL error cell, kept because it carries what `errorValue` cannot // reconstruct: a cycle's `origin`, `via` and `path`. But a source cell may hold its code only // inside its MESSAGE ("#DIV/0! This formula divides by zero.") with no `code` member, and // returning it verbatim then loses the code the reader is supposed to see in the cell. // β›” THE ASYMMETRY IS THE BUG, and it is older than this wave: the sibling catch above (the // simple-range path) has always returned `errorValue(err.message)`, which PARSES the code out // of the message, so `=SUM(A1:A3)`, `=MIN(...)` and `=MAX(...)` over an error answered // `{code: "#DIV/0!"}` while `=AVERAGE(...)` β€” the one user-facing aggregate that reaches the // AST path rather than the fast one β€” answered the same error with no code at all. // W61-T05 added the AVERAGE leg that finally asked, and the lanes never saw it fail because // the gate was aborting on a wall-clock budget before that section. // ⚠ The code is FILLED IN, never overwritten: a source that already states one keeps it, and // every other member of the source cell survives. if (err instanceof FormulaError) { const source = err.source; if (!source) return errorValue(err.message); if (source.code) return source; const parsed = errorValue(source.message); return parsed.kind === "error" && parsed.code ? { ...source, code: parsed.code } : source; } return { kind: "error", message: "This formula could not be calculated." }; } }; export interface CalculationStats { compiled: number; evaluated: number; visited: number; scanned: number; edges: number; range_checks: number; range_probes: number; reads: number; } const calculationStats = (): CalculationStats => ({ compiled: 0, evaluated: 0, visited: 0, scanned: 0, edges: 0, range_checks: 0, range_probes: 0, reads: 0 }); const dyadicSegments = (first: number, last: number): [number, number][] => { const out: [number, number][] = []; let lo = first - 1; while (lo < last) { let width = lo ? lo & -lo : 2 ** Math.floor(Math.log2(last)); while (width > last - lo) width /= 2; out.push([Math.log2(width), lo / width]); lo += width; } return out; }; class FormulaRangeIndex { private buckets = new Map<string, Set<string>>(); private owned = new Map<string, Set<string>>(); private levels = new Map<string, Map<number, number>>(); add(owner: string, bounds: FormulaRange): void { const [sheet, c0, c1, r0, r1] = bounds; const tokens = this.owned.get(owner) ?? new Set<string>(); this.owned.set(owner, tokens); for (const [cl, ci] of dyadicSegments(c0, c1)) for (const [rl, ri] of dyadicSegments(r0, r1)) { const token = JSON.stringify([sheet, cl, ci, rl, ri]); if (!this.buckets.has(token)) { const levels = this.levels.get(sheet) ?? new Map<number, number>(), level = cl*24+rl; levels.set(level, (levels.get(level) ?? 0)+1); this.levels.set(sheet, levels); } const bucket = this.buckets.get(token) ?? new Set<string>(); bucket.add(owner); this.buckets.set(token, bucket); tokens.add(token); } } remove(owner: string): void { for (const token of this.owned.get(owner) ?? []) { const bucket = this.buckets.get(token)!; bucket.delete(owner); if (!bucket.size) { this.buckets.delete(token); const [sheet, cl, , rl] = JSON.parse(token) as [string, number, number, number, number]; const levels = this.levels.get(sheet)!, level = cl*24+rl, count = levels.get(level)!-1; if (count) levels.set(level, count); else levels.delete(level); if (!levels.size) this.levels.delete(sheet); } } this.owned.delete(owner); } at(key: string, stats: CalculationStats): Set<string> { const { sheet, col, row } = splitRef(key); const out = new Set<string>(); for (const level of this.levels.get(sheet)?.keys() ?? []) { const cl = Math.floor(level/24), rl = level%24; stats.range_probes += 1; const hits = this.buckets.get(JSON.stringify([sheet, cl, (col - 1) >> cl, rl, (row - 1) >> rl])); stats.range_checks += hits?.size ?? 0; for (const hit of hits ?? []) out.add(hit); } return out; } } const lowerBound = (values: number[], target: number): number => { let lo = 0, hi = values.length; while (lo < hi) { const mid = (lo + hi) >>> 1; if (values[mid] < target) lo = mid + 1; else hi = mid; } return lo; }; /** Ordered sparse coordinates, queried by rectangle without scanning other formula nodes. */ class FormulaCellIndex { private columns = new Map<string, number[]>(); private rows = new Map<string, number[]>(); add(key: string): void { const { sheet, col, row } = splitRef(key), token = JSON.stringify([sheet, col]); let rows = this.rows.get(token); if (!rows) { const columns = this.columns.get(sheet) ?? []; columns.splice(lowerBound(columns, col), 0, col); this.columns.set(sheet, columns); rows = []; this.rows.set(token, rows); } const at = lowerBound(rows, row); if (rows[at] !== row) rows.splice(at, 0, row); } remove(key: string): void { const { sheet, col, row } = splitRef(key), token = JSON.stringify([sheet, col]); const rows = this.rows.get(token); if (!rows) return; const at = lowerBound(rows, row); if (rows[at] === row) rows.splice(at, 1); if (!rows.length) { this.rows.delete(token); const cols = this.columns.get(sheet)!; cols.splice(lowerBound(cols, col), 1); } } within(bounds: FormulaRange): string[] { const [sheet, c0, c1, r0, r1] = bounds, cols = this.columns.get(sheet) ?? []; const found: [number, number][] = []; for (let ci = lowerBound(cols, c0); ci < cols.length && cols[ci] <= c1; ci += 1) { const col = cols[ci], rows = this.rows.get(JSON.stringify([sheet, col]))!; for (let ri = lowerBound(rows, r0); ri < rows.length && rows[ri] <= r1; ri += 1) { found.push([rows[ri], col]); } } return found.sort((a, b) => a[0] - b[0] || a[1] - b[1]).map(([row, col]) => { const addr = colLetters(col) + row; return sheet ? `${sheet}!${addr}` : addr; }); } } /** Retain one instance per workbook/current-tab namespace. Reset on a snapshot, rename, switch * of current tab, or revision gap. Feed only formula edits (null removes a formula) and changed * value addresses to update; evaluate `ordered` once against the caller's final overlay getter. * Passing populatedAddresses once enables sparse aggregate evaluation; cleared addresses may * remain in that index as harmless blank reads until the next reset. */ export interface SpillRegion { version: 1; origin: string; rows: number; cols: number; range: FormulaRange; children: string[]; } export interface SpillCell { value: CellValue; spillParent: string; style: Record<string, unknown>; formulaMeta: { spillChild: SpillChildMarker; spillClear?: true }; } const compiledArrayShape = (node: SheetSyntax, lookup?: (key: string) => SheetSyntax | null | undefined, seen: readonly string[] = []): readonly [number, number] => { const shape = (child: SheetSyntax) => compiledArrayShape(child, lookup, seen); if (node.t === "range") return [node.bounds[4]-node.bounds[3]+1, node.bounds[2]-node.bounds[1]+1]; if (node.t === "name" || node.t === "unary") return shape(node.e); if (node.t === "spill" && node.e.t === "ref" && !seen.includes(node.e.v)) { const target = lookup?.(node.e.v); if (target) return compiledArrayShape(target, lookup, [...seen, node.e.v]); } if (node.t === "binary") { const a = shape(node.l), b = shape(node.r); return [Math.max(a[0], b[0]), Math.max(a[1], b[1])]; } if (node.t === "call") { if (["FILTER", "SORT"].includes(node.name) && node.args[0]) return shape(node.args[0]); if (node.name === "INDIRECT" && node.args[0]?.t === "str") { const match = /(?:^|!)(\$?[A-Za-z]{1,3}\$?[1-9][0-9]*):(\$?[A-Za-z]{1,3}\$?[1-9][0-9]*)$/.exec(node.args[0].v); if (match) { const a = splitRef(match[1].replace(/\$/g, "")), b = splitRef(match[2].replace(/\$/g, "")); return [Math.abs(a.row-b.row)+1, Math.abs(a.col-b.col)+1]; } } if (node.name === "OFFSET" && node.args[0]) { const [rows, cols] = shape(node.args[0]); return [node.args[3]?.t === "num" ? Math.max(1, Math.trunc(node.args[3].v)) : rows, node.args[4]?.t === "num" ? Math.max(1, Math.trunc(node.args[4].v)) : cols]; } if (["IF", "IFERROR"].includes(node.name)) { const shapes = node.args.slice(node.name === "IF" ? 1 : 0).map(shape); return [Math.max(1, ...shapes.map((s) => s[0])), Math.max(1, ...shapes.map((s) => s[1]))]; } if (node.name === "XLOOKUP" && node.args.length >= 3) { const source = shape(node.args[1]), result = shape(node.args[2]), wanted = shape(node.args[0]); return wanted[0]*wanted[1] > 1 ? wanted : source[1] === 1 ? [1, result[1]] : [result[0], 1]; } if (node.name === "INDEX" && node.args.length >= 2) { const [rows, cols] = shape(node.args[0]); const r = node.args[1].t === "num" ? node.args[1].v : -1; const c = node.args[2]?.t === "num" ? node.args[2].v : node.args.length > 2 ? -1 : rows > 1 && cols > 1 ? 0 : 1; return [r === 0 ? rows : 1, c === 0 ? cols : 1]; } } return [1, 1]; }; const cyclicComponents = (nodes: Set<string>, reads: Map<string, Set<string>>): string[][] => { const reverse = new Map<string, string[]>(), finished: string[] = [], seen = new Set<string>(); const neighbors = (key: string) => [...reads.get(key) ?? []].filter((ref) => nodes.has(ref)).sort(); for (const key of nodes) for (const ref of neighbors(key)) reverse.set(ref, [...reverse.get(ref) ?? [], key]); for (const root of [...nodes].sort()) { if (seen.has(root)) continue; seen.add(root); const stack: { key: string; refs: string[]; index: number }[] = [{ key: root, refs: neighbors(root), index: 0 }]; while (stack.length) { const frame = stack[stack.length-1]; if (frame.index === frame.refs.length) { finished.push(frame.key); stack.pop(); continue; } const ref = frame.refs[frame.index++]; if (!seen.has(ref)) { seen.add(ref); stack.push({ key: ref, refs: neighbors(ref), index: 0 }); } } } seen.clear(); const result: string[][] = []; for (const root of finished.reverse()) { if (seen.has(root)) continue; const stack = [root], members: string[] = []; seen.add(root); while (stack.length) { const key = stack.pop()!; members.push(key); for (const ref of reverse.get(key) ?? []) if (!seen.has(ref)) { seen.add(ref); stack.push(ref); } } if (members.length > 1 || reads.get(root)?.has(root)) result.push(members.sort()); } return result; }; export const sameFormat = (a: unknown, b: unknown): boolean => { const stable = (v: unknown): string => v && typeof v === "object" && !Array.isArray(v) ? JSON.stringify(Object.keys(v).sort().map((k) => [k, stable((v as Record<string, unknown>)[k])])) : JSON.stringify(v); return stable(a) === stable(b); }; const dynamicArrayShape = (node: SheetSyntax): boolean => { if (node.t === "call") return (node.name === "INDIRECT" && node.args[0]?.t !== "str") || (node.name === "OFFSET" && node.args.slice(3).some((arg) => arg.t !== "num")) || node.args.some(dynamicArrayShape); if (node.t === "name" || node.t === "unary" || node.t === "spill") return dynamicArrayShape(node.e); return node.t === "binary" || node.t === "refop" ? dynamicArrayShape(node.l) || dynamicArrayShape(node.r) : false; }; export class WorkbookCalculation { revision = -1; currentSheet = ""; stats = calculationStats(); readonly compiled = new Map<string, CompiledSheetFormula>(); readonly reads = new Map<string, Set<string>>(); readonly readBy = new Map<string, Set<string>>(); readonly values = new Map<string, CellValue>(); readonly spills = new Map<string, SpillRegion>(); readonly spillParents = new Map<string, string>(); readonly spillCells = new Map<string, SpillCell>(); readonly spillClears = new Map<string, string>(); ordered: string[] = []; cyclic: string[] = []; dirty = new Set<string>(); private ranges = new FormulaRangeIndex(); private formulas = new FormulaCellIndex(); private positions: FormulaCellIndex | null = null; private specs = new Map<string, QualifiedFormula>(); private pending = new Set<string>(); private volatileRoots = new Set<string>(); private volatileValues = new Map<SheetSyntax, number>(); private now = Date.now(); private options: CalculationOptions = {}; private prospective = new FormulaRangeIndex(); private prospectiveBounds = new Map<string, FormulaRange>(); private authored = new Set<string>(); private blankStyles = new Map<string, Record<string, unknown>>(); private dynamicArrays = new Set<string>(); private outputNotifications = new Set<string>(); private consumed = new Map<string, Set<string>>(); private consumedBy = new Map<string, Set<string>>(); reset(formulas: Record<string, QualifiedFormula>, revision: number, currentSheet = "", populatedAddresses?: Iterable<string>, options: CalculationOptions = {}): this { this.revision = revision; this.currentSheet = currentSheet; this.stats = calculationStats(); this.compiled.clear(); this.reads.clear(); this.readBy.clear(); this.values.clear(); this.specs.clear(); this.spills.clear(); this.spillParents.clear(); this.spillCells.clear(); this.spillClears.clear(); this.authored.clear(); this.prospective = new FormulaRangeIndex(); this.prospectiveBounds.clear(); this.blankStyles.clear(); this.dynamicArrays.clear(); this.outputNotifications.clear(); this.consumed.clear(); this.consumedBy.clear(); this.options = { ...options, namedRanges: normalizeNamedFormulaRanges(options.namedRanges) }; this.volatileRoots.clear(); this.volatileValues.clear(); this.now = Date.now(); this.ranges = new FormulaRangeIndex(); this.formulas = new FormulaCellIndex(); this.positions = populatedAddresses === undefined ? null : new FormulaCellIndex(); for (const addr of populatedAddresses ?? []) { const key = this.key(addr); this.positions!.add(key); this.authored.add(key); } for (const [raw, marker] of Object.entries(options.spillChildren ?? {})) { const key = this.key(raw), origin = this.key(marker.origin); if (marker.version !== 1 || this.key(marker.address) !== key || origin === key || splitRef(origin).sheet !== splitRef(key).sheet) continue; if (formulas[raw] || formulas[key]) continue; this.authored.delete(key); this.positions?.remove(key); this.spillClears.set(key, origin); if (marker.base?.style) this.blankStyles.set(key, marker.base.style); } for (const [addr, spec] of Object.entries(formulas)) this.put(this.key(addr), spec); for (const key of this.compiled.keys()) this.connect(key); this.schedule(new Set(this.compiled.keys())); return this; } private key(raw: string): string { const { sheet, col, row } = splitRef(String(raw).replace(/\$/g, "").toUpperCase()); return formulaReferenceKey(sheet, colLetters(col) + row, this.currentSheet); } private put(key: string, spec: QualifiedFormula | null): void { this.stats.scanned += 1; this.values.delete(key); if (!spec) { this.specs.delete(key); return; } this.specs.set(key, { ...spec }); this.positions?.add(key); this.formulas.add(key); const compiled = compileSheetFormula(spec.formula, spec.sheet, this.currentSheet, this.options.namedRanges, key); this.compiled.set(key, compiled); if (compiled.syntax && dynamicArrayShape(compiled.syntax)) this.dynamicArrays.add(key); const [rows, cols] = compiled.syntax ? compiledArrayShape(compiled.syntax) : [1, 1]; if (rows*cols > 1) { const { sheet, col, row } = splitRef(key); const bounds: FormulaRange = [sheet, col, Math.min(16384, col+cols-1), row, Math.min(1048576, row+rows-1)]; this.prospective.add(key, bounds); this.prospectiveBounds.set(key, bounds); } if (compiled.volatile) this.volatileRoots.add(key); this.stats.compiled += 1; } private connect(key: string): void { const definition = this.compiled.get(key)!; this.indexArray(key); const deps = new Set(definition.cells); for (const range of definition.ranges) { this.ranges.add(key, range); for (const ref of this.formulas.within(range)) deps.add(ref); } this.reads.set(key, deps); for (const ref of deps) { const owners = this.readBy.get(ref) ?? new Set<string>(); owners.add(key); this.readBy.set(ref, owners); this.stats.edges += 1; } } private indexArray(key: string): void { const syntax = this.compiled.get(key)?.syntax; const [rows, cols] = syntax ? compiledArrayShape(syntax, (ref) => this.compiled.get(ref)?.syntax, [key]) : [1, 1]; this.prospective.remove(key); this.prospectiveBounds.delete(key); if (rows*cols > 1) { const { sheet, col, row } = splitRef(key); const bounds: FormulaRange = [sheet, col, Math.min(16384, col+cols-1), row, Math.min(1048576, row+rows-1)]; this.prospective.add(key, bounds); this.prospectiveBounds.set(key, bounds); } } private disconnect(key: string): void { this.forgetConsumed(key); this.dynamicArrays.delete(key); this.prospective.remove(key); this.prospectiveBounds.delete(key); this.volatileRoots.delete(key); for (const ref of this.reads.get(key) ?? []) { const owners = this.readBy.get(ref)!; owners.delete(key); if (!owners.size) this.readBy.delete(ref); } this.reads.delete(key); this.ranges.remove(key); this.compiled.delete(key); this.formulas.remove(key); } private forgetConsumed(key: string): void { for (const ref of this.consumed.get(key) ?? []) { const owners = this.consumedBy.get(ref)!; owners.delete(key); if (!owners.size) this.consumedBy.delete(ref); } this.consumed.delete(key); } private consume(key: string, ref: string): void { const refs = this.consumed.get(key) ?? new Set<string>(); if (!refs.has(ref)) { refs.add(ref); this.consumed.set(key, refs); const owners = this.consumedBy.get(ref) ?? new Set<string>(); owners.add(key); this.consumedBy.set(ref, owners); } } private spillCycle(key: string, children: string[]): boolean { // Resolve a runtime child's owner before publishing values. The graph uses // consumed references, so an unselected branch cannot close a spill cycle. if (!children.some((child) => this.consumedBy.has(child))) return false; const targets = new Set(children), reachable = new Set([key]), queue = [key], edges = new Map<string, Set<string>>(); for (let i = 0; i < queue.length; i++) { const current = queue[i], deps = new Set<string>(); for (const ref of this.consumed.get(current) ?? []) { const owner = targets.has(ref) ? key : this.spillParents.get(ref) ?? ref; if (this.compiled.has(owner)) { deps.add(owner); if (!reachable.has(owner)) { reachable.add(owner); queue.push(owner); } } } edges.set(current, deps); } const component = cyclicComponents(reachable, edges).find((members) => members.includes(key)); if (!component) return false; this.cyclic = [...new Set([...this.cyclic, ...component])].sort(); for (const member of component) { this.clearSpill(member); this.values.set(member, { kind: "error", message: CYCLE_REFUSAL, origin: component[0], via: null, path: component }); this.pending.delete(member); } // A newly proved cycle invalidates already evaluated descendants as well. // They still run their own error absorbers; this is graph repair, not iteration. const descendants = [...component, ...children], seen = new Set(descendants), cyclic = new Set(this.cyclic); for (let i = 0; i < descendants.length; i++) { const ref = descendants[i]; for (const owner of new Set([...this.readBy.get(ref) ?? [], ...this.consumedBy.get(ref) ?? [], ...this.ranges.at(ref, this.stats)])) { if (seen.has(owner) || cyclic.has(owner)) continue; seen.add(owner); descendants.push(owner); if (this.compiled.has(owner)) { if (!this.dirty.has(owner)) this.stats.visited += 1; this.dirty.add(owner); this.pending.add(owner); this.ordered.push(owner); } } } return true; } private schedule(dirty: Set<string>): void { this.outputNotifications.clear(); for (const key of dirty) this.forgetConsumed(key); for (const key of dirty) this.indexArray(key); for (const key of dirty) this.clearSpill(key); const indegree = new Map<string, number>(), dependents = new Map<string, string[]>(); for (const key of dirty) { this.stats.visited += 1; let count = 0; for (const ref of this.reads.get(key) ?? []) if (dirty.has(ref)) { count += 1; this.stats.edges += 1; const owners = dependents.get(ref) ?? []; owners.push(key); dependents.set(ref, owners); } indegree.set(key, count); } const ready = [...dirty].filter((key) => !indegree.get(key)).sort(); const ordered: string[] = []; for (let i = 0; i < ready.length; i += 1) { const key = ready[i]; ordered.push(key); for (const owner of (dependents.get(key) ?? []).sort()) { const count = indegree.get(owner)! - 1; indegree.set(owner, count); if (!count) ready.push(owner); } } const settled = new Set(ordered); const blocked = [...dirty].filter((key) => !settled.has(key)).sort(); const components = cyclicComponents(new Set(blocked), this.reads); this.cyclic = components.flat().sort(); const cyclic = new Set(this.cyclic); this.ordered = [...ordered, ...blocked.filter((key) => !cyclic.has(key)), ...this.cyclic]; this.ordered = [...this.ordered.filter((key) => this.dynamicArrays.has(key)), ...this.ordered.filter((key) => !this.dynamicArrays.has(key))]; this.dirty = dirty; this.pending = new Set(dirty); for (const component of components) for (const key of component) { this.values.set(key, { kind: "error", message: CYCLE_REFUSAL, origin: component[0], via: null, path: component }); this.pending.delete(key); } } update(formulaChanges: Record<string, QualifiedFormula | null>, revision: number, changedAddresses: readonly string[] = Object.keys(formulaChanges)): this { if (revision !== this.revision + 1) throw new Error("A calculation delta requires the next revision; reset from the snapshot."); this.stats = calculationStats(); this.volatileValues.clear(); this.now = Date.now(); const changes = new Map(Object.entries(formulaChanges).map(([key, spec]) => [this.key(key), spec])); const changed = new Set([...changedAddresses.map((key) => this.key(key)), ...changes.keys()]); this.spillClears.clear(); const spillRoots = new Set<string>(), previousChildren = new Set<string>(); for (const key of changed) { const parent = this.spillParents.get(key); if (parent) spillRoots.add(parent); for (const owner of this.prospective.at(key, this.stats)) spillRoots.add(owner); for (const child of this.spills.get(key)?.children ?? []) previousChildren.add(child); } for (const key of changes.keys()) this.clearSpill(key); const consumers = new Map([...changes.keys()].map((key) => [key, this.ranges.at(key, this.stats)])); for (const key of changes.keys()) this.disconnect(key); for (const [key, spec] of changes) this.put(key, spec); for (const key of changes.keys()) { if (this.compiled.has(key)) this.connect(key); for (const owner of consumers.get(key) ?? []) if (!changes.has(owner)) { if (this.compiled.has(key)) { this.reads.get(owner)!.add(key); const owners = this.readBy.get(key) ?? new Set<string>(); owners.add(owner); this.readBy.set(key, owners); } else if (!this.compiled.get(owner)!.cells.includes(key)) { this.reads.get(owner)!.delete(key); this.readBy.get(key)?.delete(owner); } } } // Formula changes invalidate their own value; supplied value changes must invalidate a // cached formula too (for example a newly resolved bound input carried in the same edit). for (const key of changed) { this.positions?.add(key); this.authored.add(key); this.values.delete(key); this.spillClears.delete(key); } const roots = new Set([...changed, ...this.volatileRoots]); for (const key of [...spillRoots, ...previousChildren]) roots.add(key); const queue = [...roots], seen = new Set(roots), dirty = new Set<string>(); for (let i = 0; i < queue.length; i += 1) { const key = queue[i]; if (this.compiled.has(key)) dirty.add(key); for (const child of this.spills.get(key)?.children ?? []) if (!seen.has(child)) { seen.add(child); queue.push(child); } const area = this.prospectiveBounds.get(key); if (area && (area[2]-area[1]+1)*(area[4]-area[3]+1) <= 5000) { for (const child of denseRangeKeys(area)) if (!seen.has(child)) { seen.add(child); queue.push(child); } } const owners = new Set([...this.readBy.get(key) ?? [], ...this.consumedBy.get(key) ?? [], ...this.ranges.at(key, this.stats)]); for (const owner of owners) { this.stats.edges += 1; if (!seen.has(owner)) { seen.add(owner); queue.push(owner); } } } this.revision = revision; this.schedule(dirty); return this; } evaluate(address: string, get: Getter): CellValue { const key = this.key(address), compiled = this.compiled.get(key); if (!compiled) return this.values.get(key) ?? (this.spillClears.has(key) && !this.authored.has(key) ? { kind: "empty" } : get(key)); if (!this.pending.has(key) && this.values.has(key)) return this.values.get(key)!; // A caller must consume ordered. Refuse an out-of-order read instead of serving a stale // predecessor value; recursion here would reintroduce stack limits on deep dependency chains. const stack = [key], active = new Set(stack); while (stack.length) { const current = stack[stack.length - 1]; if (!this.pending.has(current)) { stack.pop(); active.delete(current); continue; } try { for (const ref of this.reads.get(current) ?? []) if (this.pending.has(ref)) throw new PendingReference(ref); this.evaluateOne(current, get); } catch (err) { if (!(err instanceof PendingReference)) throw err; const ref = err.message; if (active.has(ref)) { const component = stack.slice(stack.indexOf(ref)).sort(); for (const member of component) { this.values.set(member, { kind: "error", message: CYCLE_REFUSAL, origin: component[0], via: null, path: component }); this.pending.delete(member); } this.cyclic = [...new Set([...this.cyclic, ...component])].sort(); } else { stack.push(ref); active.add(ref); } continue; } stack.pop(); active.delete(current); } return this.values.get(key)!; } private evaluateOne(key: string, get: Getter): void { const compiled = this.compiled.get(key)!; this.stats.evaluated += 1; this.forgetConsumed(key); const read = workbookFormulaGetter((raw) => { const ref = this.key(raw); this.stats.reads += 1; this.consume(key, ref); const parent = this.spillParents.get(ref); if (parent && this.pending.has(parent)) throw new PendingReference(parent); const external = this.values.get(ref) ?? (this.spillClears.has(ref) && !this.authored.has(ref) ? { kind: "empty" } as CellValue : get(ref)); if (!this.compiled.has(ref) && external.kind === "empty") { const producers = [...this.prospective.at(ref, this.stats)].sort(); const producer = producers.find((owner) => this.pending.has(owner)); if (producer) throw new PendingReference(producer); for (const owner of producers) { const fault = this.values.get(owner); if (fault?.kind === "error" && fault.message === CYCLE_REFUSAL) return { ...fault, via: owner }; } } if (this.pending.has(ref)) throw new PendingReference(ref); const value = this.values.get(ref) ?? external; return value.kind === "error" ? { ...value, origin: value.origin ?? ref, via: ref } : value; }); let result = evaluateCompiledSheetFormula(compiled, read, (range) => { for (const [owner, area] of this.prospectiveBounds) { if (this.pending.has(owner) && area[0] === range[0] && Math.max(area[1], range[1]) <= Math.min(area[2], range[2]) && Math.max(area[3], range[3]) <= Math.min(area[4], range[4])) throw new PendingReference(owner); } return this.positions ? this.positions.within(range) : denseRangeKeys(range); }, { now: this.now, dateSystem: this.options.dateSystem, address: key, volatileValues: this.volatileValues, sheets: this.options.sheets, spillRange: (origin) => this.spills.get(this.key(origin))?.range }); if (result.kind === "error") { result = { ...result, origin: result.origin ?? key, via: result.via ?? null }; } const spec = this.specs.get(key)!, style = { ...spec.style }, meta = { ...spec.formulaMeta }; const inferred = meta.inferredFormat as { version?: number; format?: unknown } | undefined; if (inferred?.version === 1 && sameFormat(inferred.format, style.format)) delete style.format; delete meta.inferredFormat; if (style.format === undefined && result.style?.format && result.kind !== "error") { style.format = result.style.format; meta.inferredFormat = { version: 1, format: style.format }; } result = { ...result, style, formulaMeta: meta }; this.publishSpill(key, result, get); this.pending.delete(key); } private clearSpill(key: string): void { const old = this.spills.get(key); this.spills.delete(key); for (const child of old?.children ?? []) if (this.spillParents.get(child) === key) { this.spillParents.delete(child); this.spillCells.delete(child); this.values.delete(child); if (!this.authored.has(child)) { this.positions?.remove(child); this.spillClears.set(child, key); const style = this.blankStyles.get(child) ?? {}; this.spillCells.set(child, { value: { kind: "empty", style }, style, spillParent: key, formulaMeta: { spillClear: true, spillChild: { version: 1, origin: key, address: child, ...(Object.keys(style).length ? { base: { kind: "empty", style } } : {}) } } }); } } } private publishSpill(key: string, result: CellValue, get: Getter): void { this.clearSpill(key); const array = result.array, style = result.style ?? this.specs.get(key)?.style ?? {}; if (!array || result.kind === "error") { this.values.set(key, result); return; } const { sheet, row, col } = splitRef(key); const bounds: FormulaRange = [sheet, col, col+array.cols-1, row, row+array.rows-1]; const failure = (): void => { this.values.set(key, { kind: "error", code: "#SPILL!", message: "#SPILL! This array needs an empty range inside the worksheet.", origin: key, via: null }); }; if (bounds[2] > 16384 || bounds[4] > 1048576) { failure(); return; } const keys = denseRangeKeys(bounds), children = keys.slice(1); if (children.some((child) => this.compiled.has(child) || this.spillParents.has(child) || ((!this.spillClears.has(child) || this.authored.has(child)) && get(child).kind !== "empty"))) { failure(); return; } if (this.spillCycle(key, children)) { this.prospective.add(key, bounds); this.prospectiveBounds.set(key, bounds); return; } this.spills.set(key, { version: 1, origin: key, rows: array.rows, cols: array.cols, range: bounds, children }); keys.forEach((child, index) => { if (child !== key && !this.spillClears.has(child)) { const explicit = get(child).style ?? this.options.cellStyles?.[child]; if (explicit) this.blankStyles.set(child, explicit); } const base = this.blankStyles.get(child); const childStyle = child === key ? style : { ...style, ...base }; const { array: _array, ...scalar } = array.values[index], value = { ...scalar, style: childStyle, ...(child === key ? { formulaMeta: result.formulaMeta } : {}) }; this.values.set(child, value); this.positions?.add(child); if (child === key) return; this.authored.delete(child); this.spillParents.set(child, key); this.spillClears.delete(child); this.spillCells.set(child, { value, style: childStyle, spillParent: key, formulaMeta: { spillChild: { version: 1, origin: key, address: child, ...(base ? { base: { kind: "empty", style: base } } : {}) } } }); }); const queue = children.filter((child) => !this.outputNotifications.has(child)), seen = new Set(queue); for (const child of queue) this.outputNotifications.add(child); for (let i = 0; i < queue.length; i++) { const child = queue[i], owners = new Set([...this.readBy.get(child) ?? [], ...this.consumedBy.get(child) ?? [], ...this.ranges.at(child, this.stats)]); for (const owner of owners) { if (seen.has(owner)) continue; // Discovery invalidates a settled consumer only along an actual read. This // neither wakes unused branches nor retries a pending producer's values. if (this.dirty.has(owner) && (this.pending.has(owner) || !this.consumedBy.get(child)?.has(owner))) continue; seen.add(owner); queue.push(owner); if (this.compiled.has(owner)) { if (!this.dirty.has(owner)) this.stats.visited += 1; this.dirty.add(owner); this.pending.add(owner); this.ordered.push(owner); } } } } }
// sheet/formulaEval.ts β€” the CLIENT half of contract C1's calc split (wave 53, W53-T01).
//
// ⭐⭐ RULING R2: a formula that references only OTHER CELLS evaluates HERE, the instant it is
// committed, and never touches the network. `evaluateFormula` is the whole of that: a small,
// self-contained engine over literals, A1 cell references (with Excel's `$` tolerated),
// the four operators, parentheses, and C1's five aggregate functions (SUM/COUNT/AVG/MIN/MAX) plus
// COUNTA (W54-T27), each run over one simple range. It is NOT a reimplementation of the server's
// full grammar (`api/formula_runtime.py::evaluate_sheet`). W57-T06 adds scalar lookup results
// and comma-separated lookup arguments. Cross-sheet references are admitted only through
// an explicitly workbook-aware getter. W57-T07 adds five server-backed text/date functions
// through the same argument parser; unsupported forms still belong to Refresh.
//
// β›”β›” AND WHAT IT CANNOT ANSWER IS `unsupported`, NEVER AN ERROR. That distinction is the whole
// safety of shipping a second, smaller engine beside the server's: `=IF(...)` and a qualified
// reference handed to an ordinary sheet-local getter are perfectly good formulas this file simply
// does not speak, so they fall back to EXACTLY the behaviour that existed before this ticket β€” the
// cell stays `stale`, keeps its last computed value, and the server's Refresh resolves it. A caller
// must opt into qualified reads with `workbookFormulaGetter`; accepting one against an ordinary
// getter would silently read the same-named LOCAL cell, which is worse than the round trip.
//
// β›”β›” THIS FILE IMPORTS NOTHING FROM THE PROJECT, ON PURPOSE. It is compiled and driven standalone
// by `verify_formula_engine.py`, the same convention `a1.ts` already uses. A bare module with no
// project imports can never be the reason an unrelated compile's emitted root moves
// ([[tsc-outdir-root-moves-with-the-import-graph]], the lesson `sheetState.ts`'s own header
// carries). `sheetState.ts::cellToValue` is the ONE place a `Cell` β€” a type this file has never
// heard of β€” becomes the `CellValue` this file's `get` callback returns.
/**
* What one referenced cell answers with, and what `evaluateFormula` returns.
*
* ⭐⭐ `pending` COVERS TWO DIFFERENT FACTS, AND BOTH ARE CARRIED. A referenced cell with NO value
* at all yet (a bound cell never resolved) is `{ kind: "pending" }` β€” there is nothing to compute
* with, and it is treated as zero for arithmetic so a formula never blocks. A referenced cell that
* DOES have a last known value, but is itself waiting on a fresh server read, is a `number` or
* `text` value with `pending: true` riding along: the value is used for arithmetic immediately,
* and the flag rides up through every operation so the FORMULA's own result can say "this used a
* value that is not fresh" and recompute once the input resolves.
*
* ⚠ `unsupported` IS PRODUCED BY THIS FILE AND NEVER BY A GETTER. It means "the server owns this
* one", not "this is wrong"; see the header.
*/
export interface SheetArray { values: CellValue[]; rows: number; cols: number }
export type CellValue = (
| { kind: "number"; value: number; pending?: boolean }
| { kind: "text"; value: string; pending?: boolean }
| { kind: "boolean"; value: boolean; pending?: boolean }
| { kind: "empty" }
| { kind: "pending" }
| { kind: "unsupported"; reason: string }
| { kind: "error"; message: string; code?: FormulaErrorCode;
origin?: string; via?: string | null; path?: string[] }) & { array?: SheetArray; style?: Record<string, unknown>; formulaMeta?: Record<string, unknown> };
/**
* Internal aggregate keys. AVERAGE and the legacy AVG spelling share the same evaluator.
* These are not the formula catalogue or the vocabulary of a bound database rollup.
*/
export const AGGREGATE_FUNCTIONS = ["SUM", "COUNT", "AVG", "MIN", "MAX"] as const;
export type AggregateFunction = (typeof AGGREGATE_FUNCTIONS)[number];
/**
* ⭐⭐ W54-T27 / RULING R9(iii): `COUNTA` IS THE SIXTH RANGE FUNCTION, AND IT IS DELIBERATELY NOT
* A MEMBER OF THE LIST ABOVE. {@link AGGREGATE_FUNCTIONS} names internal dispatch keys.
* The bound rollup vocabulary is separately guarded in api_sheet; formula names do not define it.
*
* ⭐ IT IS SAFE TO TYPE BECAUSE THE SERVER ALREADY SPEAKS IT. `api/formula_runtime.py` carries
* `COUNTA` in both `FN_ARITY` and `FN_CATALOGUE`, and `functionCatalog.ts` already offers it in
* the suggestion list, so a `COUNTA` a person types survives the save and comes back with the
* same answer this file computes. This is the one place the two engines had to agree first.
*/
export const COUNTA_FUNCTION = "COUNTA";
/** Every function name this engine reads ahead of one simple range: the five, plus `COUNTA`. */
export const RANGE_FUNCTIONS = [...AGGREGATE_FUNCTIONS, COUNTA_FUNCTION] as const;
const AGGREGATE_SET: ReadonlySet<string> = new Set<string>(RANGE_FUNCTIONS);
/**
* ⭐⭐ THE ONE SENTENCE A CYCLE STATES, DECLARED HERE SO ONE FILE OWNS IT. `sheetState.ts`
* imports this rather than spelling it, because a refusal message a reader sees in a cell and a
* gate asserts by text must have exactly one source. Sentence case, and no dash of either width
* (standing rule 2): every `FormulaError` message in this file reaches a cell.
*/
export const CYCLE_REFUSAL = "This formula refers back to itself.";
// ── W54-T26 / RULING R9(ii): AN ERROR IS A VALUE, AND A VALUE HAS A CODE ──────────────────────
//
// ⭐⭐ WHAT WAS WRONG, MEASURED IN A BROWSER BEFORE THIS TICKET. `=1/0` painted the ENGLISH
// SENTENCE "This formula divid..." across the cell, clipped by the column, and `=FOO(1)` painted
// its own SOURCE TEXT back at the reader. Two different wrongs with one cause: an error had no
// VALUE to be, so a cell had nothing to show but prose or the formula it could not compute.
//
// β›” DESIGN.md 4, "refusals: never over-explain". A cell states a CODE. The sentence is still
// carried, first token after the code, so a disclosure can state the diagnosis without the grid
// having to; nothing about that sentence reaches the cell face.
//
// ⚠ THE CODES ARE EXCEL'S OWN TOKENS AND ARE SPELLED EXACTLY. They are not ours to prettify,
// translate or shorten: a reader who knows spreadsheets already knows what each one means, and a
// near miss (`#DIV0!`, `#NAME!`) reads as a bug in us rather than a fault in their formula.
export const ERROR_DIV0 = "#DIV/0!";