Formulas

Installation

Sheetwrite evaluates formulas in the Rust/WASM calculation engine. Formula sources are persisted exactly as document values; evaluated results are cached for rendering, queries, export, and dependent formulas.

Sheetwrite intentionally implements a coherent spreadsheet subset. It does not claim full Google Sheets or Excel formula parity.

See the generated formula function contract for the complete inventory-derived function table and the detailed compatibility results for checked operator, spill, preservation, and unsupported boundaries.

Authoring formulas

A formula is a CellValue with kind: "formula". The leading = is optional at the storage API, although UI input conventionally includes it.

grid.store.applyTransaction({
  patches: [
    {
      op: "set",
      addr: { sheet: "sheet1", row: 5, col: 4 },
      value: { kind: "formula", src: "=SUM(E1:E5)" },
    },
  ],
});

store.getFormula(address) returns the source. store.getCell(address).resolved returns the evaluated scalar or error sentinel. An unsupported function evaluates to #NAME?, but its source remains retrievable and persists through snapshots/XLSX round-trips so a later engine can evaluate it.

Supported formulas

The checked, versioned sheetwrite.formula-capabilities contract is the source of truth for function registration. Its generated reference lists every function in both engines, grouped by family, with aliases, exact signature and semantic profiles, implementation and evidence paths, source links, and behavior status. Do not infer support from an Excel, Google Sheets, or OpenFormula function with a similar name.

Browse the generated formula function contract →

Full formula engine

Sheetwrite has two engine builds with the same API. @sheetwrite/wasm is the default engine: it has the standard function set and the smallest download. @sheetwrite/formulas is the full engine: the same engine with the analysis families below. An app selects one engine, one time, before it creates a Grid or a store:

import { initSheetwrite } from "@sheetwrite/core";
import * as formulas from "@sheetwrite/formulas";

await initSheetwrite(undefined, formulas);

The default engine returns #NAME? for these functions, and formula assist does not suggest them. The formula analysis showcase runs thirteen panels of them live over 20,000 orders, and the analysis formulas guide shows each family with examples.

Limits of the full engine:

• LAMBDA parameters bind lexically, and LET can bind a LAMBDA. An uncalled LAMBDA returns #CALC!. A call with the wrong number of arguments returns #VALUE!. Calls are limited to a depth of 64 and 2,000,000 work units per outer helper; more returns #NUM!.
• GROUPBY and PIVOTBY accept one built-in function name, such as SUM, or a LAMBDA with one or two parameters. A vector of functions, such as HSTACK(SUM,AVERAGE), returns #VALUE!.
• Regex functions do not support lookaround, backreferences, or Unicode classes. Such patterns return #VALUE!, never a wrong match.
• AGGREGATE options that skip hidden rows have no effect, because formulas do not see hidden-row state.

Function names are case-insensitive. Commas are the only documented argument separator. Interior omitted optional arguments are preserved (XLOOKUP(key, keys, results,, 0)). Locale-specific separators are not accepted.

Scalars, coercion, and errors

Persisted literals and evaluated formula results use string | number | boolean | null. Boolean literals entered as TRUE/FALSE, formula comparisons, XLSX boolean cells, snapshots, visible windows, clipboard output, and CSV/TSV output remain booleans end to end. Display and text export use uppercase TRUE and FALSE.

Errors are values for display and dependency propagation, not NaN or blank rendering. IFERROR can replace them. Unsupported or malformed source remains stored even when the result is an error.

Coercion follows these documented rules:

• Arithmetic converts numeric text and booleans (TRUE = 1, FALSE = 0); nonnumeric text returns #VALUE!.
• Direct scalar boolean/text arguments may be coerced by numeric functions. Text/booleans reached through a range are ignored by numeric aggregates, matching common spreadsheet behavior.
• Empty scalar arithmetic behaves as zero. Empty range cells are skipped.
• Comparisons are case-insensitive for text and use spreadsheet type ordering.
• AND, OR, NOT, and IF accept booleans, numbers, and the text TRUE/FALSE.

LET bindings

LET(name1, value1, [name2, value2, …], calculation) supports lexical, case-insensitive local names. A name starts with an ASCII letter or _; subsequent characters may also contain ASCII digits or .. Inner LET bindings shadow outer bindings, and each value expression can see only earlier bindings.

Bindings are lazy. Only names reachable from calculation are expanded and evaluated, so an unused cell read, error, or NOW() does not become a dependency, error, or volatile marker. A used binding preserves ordinary formula dependency, spill, copy/fill, and structural-reference behavior. LET(x,1/0,7) therefore returns 7, while LET(x,A1,x+1) tracks A1.

One LET admits at most 126 bindings. Expansion admits at most 16,384 AST nodes across reachable bindings. Invalid name/arity/binding structure returns #VALUE!; expansion beyond the node ceiling returns #NUM!. In the full engine, a LET value can be a LAMBDA; see Full formula engine.

References, ranges, and named ranges

A1 cells (A1, $B12, AA$3), rectangular ranges (A1:B3), and cross-sheet references (Sales!E2, 'Sales 2026'!E2:E10) are supported. Absolute markers affect fill/structural rewriting; they do not change evaluation.

Named ranges are document operations and snapshot metadata:

store.applyTransaction({
  patches: [
    {
      op: "setNamedRange",
      namedRange: {
        name: "Revenue",
        range: {
          sheet: "data",
          start: { row: 1, col: 4 },
          end: { row: 100, col: 4 },
        },
      },
    },
    {
      op: "setNamedRange",
      namedRange: {
        name: "Revenue",
        scope: "summary",
        range: {
          sheet: "data",
          start: { row: 1, col: 5 },
          end: { row: 100, col: 5 },
        },
      },
    },
  ],
});

A formula on summary resolves the sheet-scoped Revenue; formulas on other sheets resolve the workbook definition. Names are case-insensitive, must be formula-safe identifiers, and cannot look like A1 cells or boolean literals. Row/column insertions, removals, and moves rebase their rectangles. Deleting the complete target or its scope removes the definition; formulas then fall back to a workbook definition or evaluate to #NAME?.

Array constants

Both engines accept array constants in braces. Commas separate columns and semicolons separate rows: ={1,2;3,4} spills into two rows and two columns. ={1,2,3} is one row; ={1;2;3} is one column. Normal spill limits and collision rules apply.

Elements can be numbers with an optional sign, quoted text, TRUE, FALSE, or error constants such as #N/A. Every row must have the same length. Empty arrays, references, and expressions inside braces are invalid and return #VALUE!.

Functions that accept arrays can use these values directly, for example =SUM({1,2,3}) returns 6 and =INDEX({1,2;3,4},2,1) returns 3. Copy and structural edits preserve constants and their text elements. XLSX import and export preserve the formula source.

Spill references

Put # after a cell reference to refer to the whole spill of the formula in that cell. If A1 contains =SEQUENCE(D1), then =SUM(A1#) adds all the values in the spill, and =SORT(A1#,1,-1) spills them in reverse order. When the spill changes size, formulas that use A1# use the new range. A sheet name is permitted: ='Report'!B2#.

A1# returns #REF! when the cell does not hold a spill: for example, the cell contains a value, or a collision blocks the spill (#SPILL!). If you delete the row or column of the anchor cell, the reference becomes #REF!. Both engines support spill references. XLSX files store A1# as _xlfn.ANCHORARRAY(A1); import and export convert between the two forms.

Arithmetic on a whole spill applies to each element. For example, =A1#*10 multiplies each value in the spill by 10 and spills the result.

Array arithmetic

Operators apply to each element of a range, array constant, spill reference, or dynamic-array result. This includes arithmetic (+, -, *, /, ^), text concatenation (&), comparisons, unary + and -, and %.

For example, =A1:A3*10 spills three results. =FILTER(A1:A5,B1:B5>2) uses a comparison array to select rows. =SUMPRODUCT((A1:A5>2)*B1:B5) multiplies the comparison results by the values in column B.

A scalar applies to every element. Rows and columns expand separately. A size of one expands to match the other operand. For example, ={1,2,3}+{10;20} returns {11,12,13;21,22,23}. If two sizes differ and neither is one, the result uses the larger size. Positions outside the smaller operand return #N/A.

Each element uses the scalar rules for values and errors. An error affects its element, not the whole array. Functions that accept arrays use the results directly. For example, =SUM(A1:A3*B1:B3) returns one sum. A top-level array result spills into adjacent cells. A blocked spill returns #SPILL!. Array size and memory limits still apply.

Operators

This deliberately follows Excel's spreadsheet precedence rather than programming-language conventions: =-2^2 is 4, =2^3^2 is 64, and =2^-2 is 0.25. Parenthesize formulas when portability to a non-spreadsheet evaluator matters. Percentage divides its operand by 100 and may repeat (=50%% is 0.005). Concatenation formats numbers, booleans, and blanks as spreadsheet text; arithmetic and comparison bind before &.

Comparisons return native booleans. For example, =IF(A1 >= 100, A1 * 0.9, A1).

Date/time semantics

Dates are numbers: whole days since the spreadsheet epoch, with a fractional day for time. Sheetwrite uses only the Excel 1900 date system on this path:

• serial 0 maps to 1899-12-31
• DATE(1900,1,1) = 1
• serial 60 is retained as the compatibility-only, non-Gregorian date 1900-02-29
• DATE(1900,3,1) = 61
• DATE treats 1900 as a leap year for this serial boundary and normalizes month/day overflow; DATE(2024,13,1) is 2025-01-01
• DATEVALUE is locale-neutral: it accepts the documented ISO yyyy-mm-dd form and fixed slash-delimited numeric forms; it never consults the host locale

TODAY and NOW are volatile, but never consult the clock during paint or ordinary dependency reads. The host captures one absolute instant and triggers a barrier:

store.recalculateVolatile(new Date("2026-07-13T18:00:00.000Z"));

The default captures new Date() once. The instant is converted to a UTC serial, so a fixed Date produces identical results in every host timezone. TODAY returns its whole-day component; NOW retains the fraction.

Basic TEXT date patterns are yyyy-mm-dd, yyyy/mm/dd, mm/dd/yyyy, dd/mm/yyyy, m/d/yyyy, yyyy-mm-dd hh:mm, yyyy-mm-dd hh:mm:ss, hh:mm, and hh:mm:ss.

Text evaluation is host-locale neutral. Function argument separators stay commas; VALUE uses . decimal and , grouping, and NUMBERVALUE(text, [decimal_separator], [group_separator]) uses those same defaults unless the separators are supplied explicitly. Case conversion uses Unicode mappings, not browser locale. LEN, LEFT, RIGHT, and MID count Unicode scalar values rather than UTF-16 code units. Generated text is capped at 16 MiB and bounded searches at 4,000,000 steps; excess work returns #NUM!.

Criteria semantics and shapes

Criteria strings may begin with =, <>, <, <=, >, or >=. Numeric and boolean operands are parsed before text comparison. Plain text comparisons are case-insensitive. * matches zero or more characters, ? matches one character, and ~ escapes the next wildcard. Wildcards apply to equality/inequality text criteria. Criteria are parsed once per formula evaluation, not once per cell.

Shapes are exact row-by-column dimensions; equal cell counts with different dimensions do not match:

• COUNTIF(criteria_range, criterion) accepts one matrix and one scalar criterion.
• COUNTIFS(criteria_range1, criterion1, …) requires one or more range/criterion pairs. Every criteria range must have exactly the shape of the first.
• SUMIF(criteria_range, criterion, [value_range]) and AVERAGEIF(...) use criteria_range as the value range when the third argument is omitted. When supplied, value_range must have exactly the same shape; Sheetwrite does not implement Excel's top-left range extension.
• SUMIFS(value_range, criteria_range1, criterion1, …), AVERAGEIFS, MAXIFS, and MINIFS require one or more pairs after the value range. Every criteria range must exactly match the value range.

A mismatch or malformed pair list returns #VALUE!. Matching result-range errors propagate. Text, booleans, and blanks in a numeric result range are ignored. A criteria sum with no numeric matches is 0; an average is #DIV/0!; MAXIFS/MINIFS return 0.

SUMPRODUCT and SUBTOTAL

SUMPRODUCT(array1, [array2, …]) requires at least one argument. Every argument must have the same exact row-by-column shape; a scalar is a 1×1 shape, and a dimensional mismatch returns #VALUE!. It multiplies corresponding cells and sums the products. Text, booleans, and blanks reached through ranges contribute zero; directly supplied numeric text and booleans use ordinary scalar numeric coercion. Errors propagate, and a non-finite product or total returns #NUM!.

SUBTOTAL(function_number, reference1, [reference2, …]) accepts codes 1–11: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, SUM, VAR.S, and VAR.P. It accepts up to 253 reference arguments and uses the corresponding aggregate's range coercion/error rules. Codes 101–111 are deliberately unsupported and return #VALUE! pending hidden-row provenance in formula range values. Nested-subtotal and filtered/hidden-row exclusion must not be inferred.

Financial functions

PV, FV, PMT, NPV, IRR, RATE, IPMT, and PPMT use binary64 arithmetic, fixed defaults from the generated signatures, and explicit domain errors. NPV discounts its first cash flow at period 1 and uses compensated summation. Range cash flows ignore text and blanks; errors propagate.

IRR requires at least one positive and one negative cash flow. IRR and RATE use the caller's guess (default 0.1), 14 deterministic bracket steps, at most 100 deterministic solve steps, fixed absolute/relative tolerances of 1e-12, and a finite transformed search domain. Invalid domains or failure to converge return #NUM!; no random seed, host locale, wall clock, or platform-specific iteration budget is used.

Lookup semantics

• INDEX(range, row, [column]) uses 1-based indices and returns one scalar. Zero/negative indices return #VALUE!; array-return row/column projections are not implemented.
• MATCH(key, range, 0) is exact. Match type 1 returns the largest value less than or equal to the key from ascending data. -1 returns the smallest value greater than or equal to the key from descending data. Invalid ordering returns #N/A rather than a plausible wrong row.
• VLOOKUP/HLOOKUP use exact matching when the final argument is false. The omitted/true mode uses correctly ordered approximate data and returns the largest value less than or equal to the key.
• XLOOKUP supports exact, next-smaller (-1), next-larger (1), and wildcard (2) match modes; forward/reverse and ordered binary-search modes are accepted. if_not_found is optional. If it is omitted—including via an interior empty argument—the result is #N/A.

Lookup errors in the scanned range propagate. Approximate modes validate ordering and do not silently return a result from unsorted input.

Dynamic arrays and spills

FILTER, SORT, UNIQUE, TRANSPOSE, SEQUENCE, TAKE, DROP, CHOOSECOLS, and CHOOSEROWS return rectangular values. A direct range formula such as =A1:B4 also spills:

• FILTER(array, include, [if_empty]) accepts a one-column include range matching the array's rows or a one-row include range matching its columns. Shape mismatches are #VALUE!. No selected values returns if_empty, or #CALC! when omitted.
• SORT(array, [sort_index], [sort_order], [by_col]) defaults to the first column, ascending. sort_index selects the row/column key; sort_order is 1 or -1; by_col=TRUE sorts columns instead of rows. For several keys, use SORTBY in the full engine.
• UNIQUE(array, [by_col], [exactly_once]) preserves first-seen order. by_col=TRUE compares columns; exactly_once=TRUE keeps only items occurring once. An empty result is #CALC!.
• TRANSPOSE(array) swaps rows and columns.
• SEQUENCE(rows, [columns], [start], [step]) requires positive integer dimensions and defaults to one column, start 1, step 1; values fill row-major.
• TAKE/DROP(array, rows, [columns]) use positive counts from the start and negative counts from the end. A zero or empty result is #CALC!; counts beyond an axis clamp to that axis.
• CHOOSECOLS/CHOOSEROWS(array, index1, …) use 1-based positive indices and end-relative negative indices. Repeated indices repeat output axes; zero/out-of-range indices return #VALUE!.

Errors inside a returned array stay at their corresponding spill children; a mixed result such as {1;#N/A;2} does not collapse to an anchor-only error.

The formula cell is the anchor and owns the complete runtime rectangle. Spill children have no formula source or persisted document identity. store.getSpillAnchor(address) returns the anchor for either the anchor or a child, and returns null for an ordinary cell.

Spills publish atomically. Any nonempty destination, another spill, merge, validation or protected range, unloaded paged cell, or sheet boundary makes the anchor #SPILL!; no partial children remain. Styles, conditional formatting, and hidden rows/columns do not obstruct. Editing a projected child through canonical core mutations is rejected. Edit or clear the anchor instead. Structural and metadata changes invalidate ownership and recompute it.

Snapshots and XLSX export serialize only the anchor formula. Children recompute after hydration. Internal rich copy preserves the anchor formula while treating children as derived blanks on paste; external TSV receives the displayed spill values. History snapshots likewise restore the anchor and recompute children, so undo never persists stale projections. Unsupported OOXML array/data-table formula records remain inert source text with an explicit warning rather than being silently treated as Sheetwrite spills.

Each array dimension is capped at Excel's row/column limits, one spill is capped at 1,000,000 cells and 64 MiB of bounded value/intermediate storage, and one dynamic recompute pass is capped at 2,000,000 cell operations. Materialized spill ownership across the complete store is also capped at 1,000,000 cells and 64 MiB, including per-child error metadata. Installation checks that cumulative budget atomically; clearing, shrinking, or removing a spill releases its ownership. Oversized work returns stable #NUM! without partial children, while unstable or colliding shapes return an explicit error rather than truncating.

The admission check happens before materialization when the shape is statically knowable and is repeated before spill ownership is installed. It accounts source/result copies and per-item auxiliary storage; a shape that could exceed a ceiling is rejected rather than optimistically allocated. A recompute either atomically replaces the complete previous spill or leaves no projected children.

Unsupported categories

Formula evaluation performs no network request and runs no custom JavaScript. Automatic volatile functions beyond the explicit TODAY/NOW host-clock barrier, network functions, external/live-data providers, arbitrary external workbook references, and cube/OLAP functions are unsupported. Examples include RAND, RANDBETWEEN, INDIRECT, OFFSET, WEBSERVICE, GOOGLEFINANCE, IMPORT*, RTD, and CUBEVALUE. Database functions such as DSUM and LAMBDA helpers such as MAP are in the full engine only.

Unsupported names evaluate to #NAME? while preserving source for snapshots and interchange. There is no compatibility shim or side-effecting fallback. The generated unsupported-category ledger is derived from the versioned inventory.

Performance evidence

The default and full formula engine results compare both engines on the formula benchmark and time the analysis families. Follow the performance evidence guide to capture and freshness-check measurements; do not treat resource ceilings as benchmark results.

Point mode and reference rewriting

While editing a formula, clicking a cell inserts its A1 reference and dragging inserts a range. Relative references shift during fill; $-absolute axes remain fixed. Structural row/column edits rewrite direct and named references while preserving stable sheet identity.

The same $-aware utility is public:

import { shiftA1Refs } from "@sheetwrite/core";

shiftA1Refs("A1 + $B$1", 0, 1); // "B1 + $B$1"

A1 address helpers are exported as cellA1, colToA1, labelToCol, and rangeA1.