Analysis formulas

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.