Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
Download web/src/sheet/formulaEval.ts from fsanyoto/runloopable: direct link, hf CLI and curl.
- Browser
- Download file 168 kB
-
https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/web/src/sheet/formulaEval.ts
- Command line
-
hf download hf://spaces/fsanyoto/runloopable/web/src/sheet/formulaEval.ts
-
curl -L -o formulaEval.ts https://huggingface.co/spaces/fsanyoto/runloopable/resolve/main/web/src/sheet/formulaEval.ts
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!";