Analysis formulas

Use MAP with a LAMBDA to calculate one result for each input value.

grid.store.applyTransaction({
  patches: [
    { op: "set", addr: { sheet: "sheet1", row: 0, col: 0 },
      value: { kind: "formula", src: "=MAP({1;2;3},LAMBDA(x,x*2))" } },
  ],
});

Try it live — formula showcase. Edit a formula to try this example.

import { createGrid, initSheetwrite } from "@sheetwrite/core";
import "@sheetwrite/core/styles.css";
import * as formulas from "@sheetwrite/formulas";

const host = document.querySelector<HTMLElement>("#grid");
if (host === null) throw new Error("Missing #grid host");
host.style.height = "320px";

await initSheetwrite(undefined, formulas);
const grid = createGrid(host, {
  workbook: {
    activeSheet: "sheet1",
    sheets: [{
      id: "sheet1", name: "Sheet1", rowCount: 3,
      columns: [
        { key: "result", header: "Result", type: "number", width: 120 },
      ],
    }],
  },
  data: { rowCount: 3, columns: { result: [null, null, null] } },
});

grid.store.applyTransaction({
  patches: [
    { op: "set", addr: { sheet: "sheet1", row: 0, col: 0 },
      value: { kind: "formula", src: "=MAP({1;2;3},LAMBDA(x,x*2))" } },
  ],
});

The full formula engine, @sheetwrite/formulas, adds analysis functions to the default engine: grouping and pivot tables, LAMBDA helpers, statistics, regression, distributions, finance, dates, text and regex, reshaping, database functions, and matrices. This guide shows one task for each family. The formula function contract lists every function, its signature, and its build. The formulas guide explains syntax, references, errors, and spills. To see these functions recalculate live over 20,000 orders, open the formula analysis showcase.

Select the engine

Select the full engine one time, before you create a Grid or a store. The rest of the API does not change.

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

await initSheetwrite(undefined, formulas);

With the default engine, these functions return #NAME?, and formula assist does not suggest them.

The example data

All examples use this sheet of 12 orders in A1:G12. Column E holds Units × Price, and column F holds order dates.

Cash flows for the finance examples are in H1:I4: −10,000 on 2026-01-01, 2,500 on 2026-04-01, 4,200 on 2026-09-01, and 6,000 on 2026-12-31.

Every result below comes from the full engine. Results that spill are shown as the cells they fill.

Summarize: GROUPBY, PIVOTBY, PERCENTOF

GROUPBY groups rows by one or more key columns and aggregates the values. It adds a total row by default.

=GROUPBY(A1:A12, E1:E12, SUM)

PIVOTBY adds column keys:

=PIVOTBY(A1:A12, B1:B12, E1:E12, SUM)

The optional arguments control headers, totals, and sort order. PERCENTOF as the function gives each group's share, and a LAMBDA can be the function:

A vector of functions, such as HSTACK(SUM, AVERAGE), returns #VALUE!. Use one function per GROUPBY.

Your own functions: LAMBDA, MAP, REDUCE, SCAN, BYROW

LAMBDA makes a function from parameters and a calculation. The helpers call it for each value, row, or column.

LET can name a LAMBDA and call it later. An uncalled LAMBDA returns #CALC!, and a call with the wrong number of arguments returns #VALUE!. Calls nest up to 64 deep.

Describe: statistics and ranking

Forecast: regression

Monthly revenue for six months was 1200, 1350, 1500, 1580, 1700, and 1850.

LINEST and LOGEST accept more than one predictor column, the const argument, and stats = TRUE for five rows of fit statistics. TREND and GROWTH project new values from the same fit.

Estimate risk: distributions

The engine has 36 distribution functions: normal, Student t, chi-squared, F, binomial, Poisson, exponential, gamma, beta, lognormal, and Weibull, with their inverses.

Plan money: finance

The full engine also has MIRR, NPER, CUMPRINC, EFFECT, NOMINAL, and depreciation functions (SLN, DB, DDB, SYD).

Schedule: working days and dates

The weekend argument is a number or a seven-character mask that starts on Monday: "0000011" means Saturday and Sunday. A holiday range is the optional last argument.

Clean text: split, extract, and regex

Regex uses the regex-lite syntax. Patterns with lookaround, backreferences, or Unicode classes return #VALUE!, never a wrong match. Case-insensitive matching folds ASCII letters only.

Reshape arrays

SORTBY keeps equal items in their order. TOCOL and TOROW can skip blanks and errors and read by row or by column.

Query tables: database functions

Database functions take a table with a header row, a field, and a criteria table with the same headers. Criteria columns use AND, and criteria rows use OR.

In a workbook, the table and the criteria are usually ranges, such as =DSUM(A1:E200, "Revenue", H1:H2).

Matrices

Limits

• All these functions use the normal spill, shape, memory, and work limits. A result that does not fit returns an error, not a partial result.
• AGGREGATE options that skip hidden rows have no effect, because formulas do not see hidden-row state.
• Each family's exact argument rules are in the 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.