Formula function contract
This page is generated from the checked version 1 sheetwrite.formula-capabilities inventory. It publishes 100 required-supported target functions, 54 incumbent functions, and 145 optional analysis functions (299 canonical functions total) without maintaining a second name list. Aliases share their canonical function's build availability.
A function's presence means only the signature and semantic profiles linked in its row. Microsoft Excel documentation supplies the naming/family taxonomy; it is not a blanket Excel claim. Google Sheets and OpenFormula behavior is unverified unless a dialect profile says otherwise.
Engine builds
@sheetwrite/wasmis the default engine. In the function tables, the Engine column showsdefault, fullfor a function in both engines andfullfor a function in@sheetwrite/formulasonly.@sheetwrite/formulasis the full engine. It adds the analysis families marked below: distributions and statistics, regression, finance, dates, text and regex, database, math and matrices, reshaping, LAMBDA helpers, and grouping. Select it before creating any stores:import * as formulas from "@sheetwrite/formulas"; await initSheetwrite(undefined, formulas);.- The active engine's registered names drive formula assist. Optional names are not suggested by the default engine and evaluate to
#NAME?there; merely importing the optional package does not enable them. - Numeric functions use binary64 arithmetic with function-specific domains and bounded numerical algorithms. The inventory does not claim unlimited tail accuracy or complete Excel/Google Sheets/OpenFormula parity.
Bounded evaluation contract
- Parsing and dependency evaluation are each capped at 256 recursive levels. Range and matrix work is capped at 1,000,000 cells, 1,048,576 rows, 16,384 columns, and 64 MiB of value/intermediate storage; excess work returns an explicit formula error rather than truncating.
- One dynamic-array recompute pass is capped at 2,000,000 cell operations. Spill installation is atomic and collision-checked; see the formula guide for admission rules.
LETpermits at most 126 bindings and 16,384 expanded AST nodes. Bindings are lexical, shadow outer bindings, and are expanded lazily, so unused reads, errors, and volatility do not become dependencies.- Generated text is capped at 16 MiB and bounded searches at 4,000,000 steps. Text case conversion and parsing are Unicode-aware and host-locale independent;
NUMBERVALUEdefaults to.decimal and,grouping separators unless supplied explicitly. IRRandRATEuse deterministic root solving: 14 bracket steps, at most 100 solve steps, fixed1e-12convergence tolerances, and a finite search domain. Non-convergence or an invalid domain returns#NUM!.
No formula throughput or latency number is published here because this contract has no checked final formula-performance artifact. Use the performance evidence protocol to capture and validate measurements; limits above are implementation ceilings, not benchmark results.
Functions by family
Dynamic array
Taxonomy/source: Lookup and reference functions.
Date and time
Taxonomy/source: Date and time functions.
Financial
Taxonomy/source: Financial functions.
Information and error
Taxonomy/source: Information functions.
Logical and control flow
Taxonomy/source: Logical functions.
Lookup and reference
Taxonomy/source: Lookup and reference functions.
Math and trigonometry
Taxonomy/source: Math and trigonometry functions.
Statistical
Taxonomy/source: Statistical functions.
Text
Taxonomy/source: Text functions.
Formula operators
Taxonomy/source: Types of operators.
| Function (aliases) | Engine | Contract | Signature | Semantics | Dialect | Implementation |
|---|
Database
Taxonomy/source: Database functions.
Signature profiles
Argument order, required/default state, accepted shapes, repetition, and return shape come directly from the inventory.
Signature: text-delimiter
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
text | yes | none | scalar, reference | once |
delimiter | yes | none | scalar, reference | once |
instanceNum | no | 1 | scalar, reference | once |
matchMode | no | 0 | scalar, reference | once |
matchEnd | no | 0 | scalar, reference | once |
ifNotFound | no | #N/A | scalar, reference | once |
Signature: text-split
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
text | yes | none | scalar, reference | once |
colDelimiter | no | none | scalar, range, array, reference | once |
rowDelimiter | no | none | scalar, range, array, reference | once |
ignoreEmpty | no | false | scalar, reference | once |
matchMode | no | 0 | scalar, reference | once |
padWith | no | #N/A | scalar, reference | once |
Signature: text-fixed
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
number | yes | none | scalar, reference | once |
decimals | no | 2 | scalar, reference | once |
noCommas | no | false | scalar, reference | once |
Signature: text-dollar
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
number | yes | none | scalar, reference | once |
decimals | no | 2 | scalar, reference | once |
Signature: value-to-text
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
value | yes | none | scalar, reference | once |
format | no | 0 | scalar, reference | once |
Signature: array-to-text
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | scalar, range, array, reference | once |
format | no | 0 | scalar, reference | once |
Signature: regex-test
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
text | yes | none | scalar, reference | once |
pattern | yes | none | scalar, reference | once |
caseSensitivity | no | 0 | scalar, reference | once |
Signature: regex-extract
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
text | yes | none | scalar, reference | once |
pattern | yes | none | scalar, reference | once |
returnMode | no | 0 | scalar, reference | once |
caseSensitivity | no | 0 | scalar, reference | once |
Signature: regex-replace
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
text | yes | none | scalar, reference | once |
pattern | yes | none | scalar, reference | once |
replacement | yes | none | scalar, reference | once |
occurrence | no | 0 | scalar, reference | once |
caseSensitivity | no | 0 | scalar, reference | once |
Signature: zero
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
| none | — | — | — | — |
Signature: unary-value
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
value | yes | none | scalar, range, array, reference | once |
Signature: unary-number
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
number | yes | none | scalar, reference | once |
Signature: binary-number
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
number1 | yes | none | scalar, reference | once |
number2 | yes | none | scalar, reference | once |
Signature: variadic-values
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
value | yes | none | scalar, range, array, reference | one-or-more |
Signature: variadic-values-array
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
value | yes | none | scalar, range, array, reference | one-or-more |
Signature: optional-variadic-values
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
value | no | none | scalar, range, array, reference | zero-or-more |
Signature: logical-variadic
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
logical | yes | none | scalar, range, array, reference | one-or-more |
Signature: if
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
logicalTest | yes | none | scalar, reference | once |
valueIfTrue | no | true | scalar, range, array, reference | once |
valueIfFalse | no | false | scalar, range, array, reference | once |
Signature: if-error
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
value | yes | none | scalar, range, array, reference | once |
valueIfError | yes | none | scalar, range, array, reference | once |
Signature: ifs
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
testAndValue | yes | none | scalar, range, array, reference | paired |
Signature: switch
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
expression | yes | none | scalar, reference | once |
valueAndResult | yes | none | scalar, range, array, reference | paired |
defaultValue | no | #N/A | scalar, range, array, reference | once |
Signature: let
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
nameAndValue | yes | none | scalar, range, array, name, reference | paired |
calculation | yes | none | scalar, range, array, name, reference | once |
Signature: round
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
number | yes | none | scalar, reference | once |
digits | yes | none | scalar, reference | once |
Signature: trunc
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
number | yes | none | scalar, reference | once |
digits | no | 0 | scalar, reference | once |
Signature: log
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
number | yes | none | scalar, reference | once |
base | no | 10 | scalar, reference | once |
Signature: subtotal
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
functionNumber | yes | none | scalar, reference | once |
reference | yes | none | range, array, reference | one-or-more |
Signature: left-right
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
text | yes | none | scalar, reference | once |
count | no | 1 | scalar, reference | once |
Signature: mid
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
text | yes | none | scalar, reference | once |
start | yes | none | scalar, reference | once |
count | yes | none | scalar, reference | once |
Signature: text-format
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
value | yes | none | scalar, reference | once |
formatText | yes | none | scalar, reference | once |
Signature: text-join
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
delimiter | yes | none | scalar, reference | once |
ignoreEmpty | yes | none | scalar, reference | once |
text | yes | none | scalar, range, array, reference | one-or-more |
Signature: substitute
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
text | yes | none | scalar, reference | once |
oldText | yes | none | scalar, reference | once |
newText | yes | none | scalar, reference | once |
instance | no | all | scalar, reference | once |
Signature: replace
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
oldText | yes | none | scalar, reference | once |
start | yes | none | scalar, reference | once |
count | yes | none | scalar, reference | once |
newText | yes | none | scalar, reference | once |
Signature: find-search
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
findText | yes | none | scalar, reference | once |
withinText | yes | none | scalar, reference | once |
start | no | 1 | scalar, reference | once |
Signature: repeat-text
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
text | yes | none | scalar, reference | once |
count | yes | none | scalar, reference | once |
Signature: number-value
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
text | yes | none | scalar, reference | once |
decimalSeparator | no | . | scalar, reference | once |
groupSeparator | no | , | scalar, reference | once |
Signature: date-three
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
part1 | yes | none | scalar, reference | once |
part2 | yes | none | scalar, reference | once |
part3 | yes | none | scalar, reference | once |
Signature: date-pair
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
endDate | yes | none | scalar, reference | once |
startDate | yes | none | scalar, reference | once |
Signature: date-offset
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
startDate | yes | none | scalar, reference | once |
offset | yes | none | scalar, reference | once |
Signature: weekday-weeknum
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
serialNumber | yes | none | scalar, reference | once |
returnType | no | 1 | scalar, reference | once |
Signature: workday
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
startDate | yes | none | scalar, reference | once |
days | yes | none | scalar, reference | once |
holidays | no | none | range, array, reference | once |
Signature: networkdays
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
startDate | yes | none | scalar, reference | once |
endDate | yes | none | scalar, reference | once |
holidays | no | none | range, array, reference | once |
Signature: yearfrac-days360
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
startDate | yes | none | scalar, reference | once |
endDate | yes | none | scalar, reference | once |
basisOrMethod | no | 0 | scalar, reference | once |
Signature: value-k
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
k | yes | none | scalar, reference | once |
Signature: rank
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
number | yes | none | scalar, reference | once |
reference | yes | none | range, array, reference | once |
order | no | 0 | scalar, reference | once |
Signature: percentile
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
fraction | yes | none | scalar, reference | once |
Signature: pair-arrays
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array1 | yes | none | range, array, reference | once |
array2 | yes | none | range, array, reference | once |
Signature: criteria-one
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
range | yes | none | range, array, reference | once |
criteria | yes | none | scalar, reference | once |
Signature: criteria-many
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
rangeAndCriteria | yes | none | scalar, range, array, reference | paired |
Signature: sumif
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
range | yes | none | range, array, reference | once |
criteria | yes | none | scalar, reference | once |
sumRange | no | range | range, array, reference | once |
Signature: sumifs
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
sumRange | yes | none | range, array, reference | once |
rangeAndCriteria | yes | none | scalar, range, array, reference | paired |
Signature: match
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
lookupValue | yes | none | scalar, reference | once |
lookupArray | yes | none | range, array, reference | once |
matchMode | no | 1 | scalar, reference | once |
Signature: xmatch
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
lookupValue | yes | none | scalar, reference | once |
lookupArray | yes | none | range, array, reference | once |
matchMode | no | 0 | scalar, reference | once |
searchMode | no | 1 | scalar, reference | once |
Signature: index
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
row | yes | none | scalar, reference | once |
column | no | 1 | scalar, reference | once |
Signature: xlookup
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
lookupValue | yes | none | scalar, reference | once |
lookupArray | yes | none | range, array, reference | once |
returnArray | yes | none | range, array, reference | once |
ifNotFound | no | #N/A | scalar, reference | once |
matchMode | no | 0 | scalar, reference | once |
searchMode | no | 1 | scalar, reference | once |
Signature: table-lookup
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
lookupValue | yes | none | scalar, reference | once |
table | yes | none | range, array, reference | once |
index | yes | none | scalar, reference | once |
approximate | no | true | scalar, reference | once |
Signature: filter
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
include | yes | none | range, array, reference | once |
ifEmpty | no | #CALC! | scalar, reference | once |
Signature: sort
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
sortIndex | no | 1 | scalar, reference | once |
sortOrder | no | 1 | scalar, reference | once |
byColumn | no | false | scalar, reference | once |
Signature: unique
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
byColumn | no | false | scalar, reference | once |
exactlyOnce | no | false | scalar, reference | once |
Signature: choose
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
index | yes | none | scalar, reference | once |
value | yes | none | scalar, range, array, reference | one-or-more |
Signature: optional-reference
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
reference | no | formula-cell | range, array, reference | once |
Signature: address
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
row | yes | none | scalar, reference | once |
column | yes | none | scalar, reference | once |
absNumber | no | 1 | scalar, reference | once |
a1 | no | true | scalar, reference | once |
sheetText | no | none | scalar, reference | once |
Signature: array-unary
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
Signature: sequence
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
rows | yes | none | scalar, reference | once |
columns | no | 1 | scalar, reference | once |
start | no | 1 | scalar, reference | once |
step | no | 1 | scalar, reference | once |
Signature: take-drop
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
rows | yes | none | scalar, reference | once |
columns | no | all | scalar, reference | once |
Signature: sort-by
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
byArrayAndOrder | yes | none | scalar, range, array, reference | paired |
Signature: choose-axis
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
index | yes | none | scalar, array, reference | one-or-more |
Signature: pv-fv-pmt
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
rate | yes | none | scalar, reference | once |
nper | yes | none | scalar, reference | once |
paymentOrPresentValue | yes | none | scalar, reference | once |
futureValue | no | 0 | scalar, reference | once |
type | no | 0 | scalar, reference | once |
Signature: npv
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
rate | yes | none | scalar, reference | once |
value | yes | none | scalar, range, array, reference | one-or-more |
Signature: irr
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
values | yes | none | range, array, reference | once |
guess | no | 0.1 | scalar, reference | once |
Signature: rate
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
nper | yes | none | scalar, reference | once |
payment | yes | none | scalar, reference | once |
presentValue | yes | none | scalar, reference | once |
futureValue | no | 0 | scalar, reference | once |
type | no | 0 | scalar, reference | once |
guess | no | 0.1 | scalar, reference | once |
Signature: period-payment
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
rate | yes | none | scalar, reference | once |
period | yes | none | scalar, reference | once |
nper | yes | none | scalar, reference | once |
presentValue | yes | none | scalar, reference | once |
futureValue | no | 0 | scalar, reference | once |
type | no | 0 | scalar, reference | once |
Signature: norm-dist
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
mean | yes | none | scalar, reference | once |
standardDeviation | yes | none | scalar, reference | once |
cumulative | yes | none | scalar, reference | once |
Signature: norm-inv
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
probability | yes | none | scalar, reference | once |
mean | yes | none | scalar, reference | once |
standardDeviation | yes | none | scalar, reference | once |
Signature: norm-s-dist
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
z | yes | none | scalar, reference | once |
cumulative | yes | none | scalar, reference | once |
Signature: t-dist
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
degreesFreedom | yes | none | scalar, reference | once |
cumulative | yes | none | scalar, reference | once |
Signature: distribution-tail
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
degreesFreedom | yes | none | scalar, reference | once |
Signature: distribution-inverse
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
probability | yes | none | scalar, reference | once |
degreesFreedom | yes | none | scalar, reference | once |
Signature: f-dist
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
degreesFreedom1 | yes | none | scalar, reference | once |
degreesFreedom2 | yes | none | scalar, reference | once |
cumulative | yes | none | scalar, reference | once |
Signature: f-tail
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
degreesFreedom1 | yes | none | scalar, reference | once |
degreesFreedom2 | yes | none | scalar, reference | once |
Signature: f-inverse
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
probability | yes | none | scalar, reference | once |
degreesFreedom1 | yes | none | scalar, reference | once |
degreesFreedom2 | yes | none | scalar, reference | once |
Signature: binom-dist
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
successes | yes | none | scalar, reference | once |
trials | yes | none | scalar, reference | once |
probability | yes | none | scalar, reference | once |
cumulative | yes | none | scalar, reference | once |
Signature: poisson-dist
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
mean | yes | none | scalar, reference | once |
cumulative | yes | none | scalar, reference | once |
Signature: expon-dist
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
lambda | yes | none | scalar, reference | once |
cumulative | yes | none | scalar, reference | once |
Signature: gamma-dist
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
alpha | yes | none | scalar, reference | once |
beta | yes | none | scalar, reference | once |
cumulative | yes | none | scalar, reference | once |
Signature: gamma-inverse
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
probability | yes | none | scalar, reference | once |
alpha | yes | none | scalar, reference | once |
beta | yes | none | scalar, reference | once |
Signature: beta-dist
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
alpha | yes | none | scalar, reference | once |
beta | yes | none | scalar, reference | once |
cumulative | yes | none | scalar, reference | once |
lower | no | 0 | scalar, reference | once |
upper | no | 1 | scalar, reference | once |
Signature: beta-inverse
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
probability | yes | none | scalar, reference | once |
alpha | yes | none | scalar, reference | once |
beta | yes | none | scalar, reference | once |
lower | no | 0 | scalar, reference | once |
upper | no | 1 | scalar, reference | once |
Signature: lognorm-dist
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
mean | yes | none | scalar, reference | once |
standardDeviation | yes | none | scalar, reference | once |
cumulative | yes | none | scalar, reference | once |
Signature: lognorm-inverse
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
probability | yes | none | scalar, reference | once |
mean | yes | none | scalar, reference | once |
standardDeviation | yes | none | scalar, reference | once |
Signature: weibull-dist
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
alpha | yes | none | scalar, reference | once |
beta | yes | none | scalar, reference | once |
cumulative | yes | none | scalar, reference | once |
Signature: confidence
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
alpha | yes | none | scalar, reference | once |
standardDeviation | yes | none | scalar, reference | once |
size | yes | none | scalar, reference | once |
Signature: standardize
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
mean | yes | none | scalar, reference | once |
standardDeviation | yes | none | scalar, reference | once |
Signature: trim-mean
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
percent | yes | none | scalar, reference | once |
Signature: percent-rank
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
x | yes | none | scalar, reference | once |
significance | no | 3 | scalar, reference | once |
Signature: known-y-x
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
knownYs | yes | none | range, array, reference | once |
knownXs | yes | none | range, array, reference | once |
Signature: forecast
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
knownYs | yes | none | range, array, reference | once |
knownXs | yes | none | range, array, reference | once |
Signature: datedif
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
startDate | yes | none | scalar, reference | once |
endDate | yes | none | scalar, reference | once |
unit | yes | none | scalar, reference | once |
Signature: calendar-intl
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
startDate | yes | none | scalar, reference | once |
endDateOrDays | yes | none | scalar, reference | once |
weekend | no | 1 | scalar, reference | once |
holidays | no | none | scalar, range, array, reference | once |
Signature: nper
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
rate | yes | none | scalar, reference | once |
payment | yes | none | scalar, reference | once |
presentValue | yes | none | scalar, reference | once |
futureValue | no | 0 | scalar, reference | once |
type | no | 0 | scalar, reference | once |
Signature: xnpv
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
rate | yes | none | scalar, reference | once |
values | yes | none | scalar, range, array, reference | once |
dates | yes | none | scalar, range, array, reference | once |
Signature: xirr
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
values | yes | none | scalar, range, array, reference | once |
dates | yes | none | scalar, range, array, reference | once |
guess | no | 0.1 | scalar, reference | once |
Signature: mirr
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
values | yes | none | scalar, range, array, reference | once |
financeRate | yes | none | scalar, reference | once |
reinvestRate | yes | none | scalar, reference | once |
Signature: cumulative-payment
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
rate | yes | none | scalar, reference | once |
nper | yes | none | scalar, reference | once |
presentValue | yes | none | scalar, reference | once |
startPeriod | yes | none | scalar, reference | once |
endPeriod | yes | none | scalar, reference | once |
type | yes | none | scalar, reference | once |
Signature: sln
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
cost | yes | none | scalar, reference | once |
salvage | yes | none | scalar, reference | once |
life | yes | none | scalar, reference | once |
Signature: syd
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
cost | yes | none | scalar, reference | once |
salvage | yes | none | scalar, reference | once |
life | yes | none | scalar, reference | once |
period | yes | none | scalar, reference | once |
Signature: db
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
cost | yes | none | scalar, reference | once |
salvage | yes | none | scalar, reference | once |
life | yes | none | scalar, reference | once |
period | yes | none | scalar, reference | once |
month | no | 12 | scalar, reference | once |
Signature: ddb
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
cost | yes | none | scalar, reference | once |
salvage | yes | none | scalar, reference | once |
life | yes | none | scalar, reference | once |
period | yes | none | scalar, reference | once |
factor | no | 2 | scalar, reference | once |
Signature: rri
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
nper | yes | none | scalar, reference | once |
presentValue | yes | none | scalar, reference | once |
futureValue | yes | none | scalar, reference | once |
Signature: pduration
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
rate | yes | none | scalar, reference | once |
presentValue | yes | none | scalar, reference | once |
futureValue | yes | none | scalar, reference | once |
Signature: fvschedule
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
principal | yes | none | scalar, reference | once |
schedule | yes | none | scalar, range, array, reference | once |
Signature: regression-fit
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
knownY | yes | none | range, array, reference | once |
knownX | no | 1,2,3,... | range, array, reference | once |
const | no | true | scalar, reference | once |
stats | no | false | scalar, reference | once |
Signature: regression-predict
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
knownY | yes | none | range, array, reference | once |
knownX | no | 1,2,3,... | range, array, reference | once |
newX | no | knownX | range, array, reference | once |
const | no | true | scalar, reference | once |
Signature: frequency
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
data | yes | none | range, array, reference | once |
bins | yes | none | range, array, reference | once |
Signature: probability-range
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
xRange | yes | none | range, array, reference | once |
probRange | yes | none | range, array, reference | once |
lowerLimit | no | 0 | scalar, reference | once |
upperLimit | no | lowerLimit | scalar, reference | once |
Signature: database
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
database | yes | none | range, reference | once |
field | yes | none | scalar, reference | once |
criteria | yes | none | range, reference | once |
Signature: round-math
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
number | yes | none | scalar, reference | once |
significance | no | 1 | scalar, reference | once |
mode | no | 0 | scalar, reference | once |
Signature: series-sum
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
x | yes | none | scalar, reference | once |
n | yes | none | scalar, reference | once |
m | yes | none | scalar, reference | once |
coefficients | yes | none | range, array, reference | once |
Signature: aggregate
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
functionNumber | yes | none | scalar, reference | once |
options | yes | none | scalar, reference | once |
reference | yes | none | range, array, reference | one-or-more |
Signature: matrix-pair
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array1 | yes | none | range, array, reference | once |
array2 | yes | none | range, array, reference | once |
Signature: matrix-unary
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
Signature: matrix-unit
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
dimension | yes | none | scalar, reference | once |
Signature: reshape-flatten
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | scalar, range, array, reference | once |
ignore | no | 0 | scalar, reference | once |
scanByColumn | no | FALSE | scalar, reference | once |
Signature: reshape-wrap
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
vector | yes | none | scalar, range, array, reference | once |
wrapCount | yes | none | scalar, reference | once |
padWith | no | #N/A | scalar, reference | once |
Signature: reshape-expand
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | scalar, range, array, reference | once |
rows | yes | none | scalar, reference | once |
columns | no | source columns | scalar, reference | once |
padWith | no | #N/A | scalar, reference | once |
Signature: reshape-stack
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | scalar, range, array, reference | one-or-more |
Signature: lambda-lambda
Return shape: contextual.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
parameter | no | none | scalar, range, array, reference | zero-or-more |
calculation | yes | none | scalar, range, array, reference | once |
Signature: lambda-map
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | scalar, range, array, reference | one-or-more |
lambda | yes | none | scalar, range, array, reference | once |
Signature: lambda-reduce
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
initialValue | no | none | scalar, range, array, reference | once |
array | yes | none | scalar, range, array, reference | once |
lambda | yes | none | scalar, range, array, reference | once |
Signature: lambda-scan
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
initialValue | no | none | scalar, range, array, reference | once |
array | yes | none | scalar, range, array, reference | once |
lambda | yes | none | scalar, range, array, reference | once |
Signature: lambda-byrow
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | scalar, range, array, reference | once |
lambda | yes | none | scalar, range, array, reference | once |
Signature: lambda-bycol
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | scalar, range, array, reference | once |
lambda | yes | none | scalar, range, array, reference | once |
Signature: lambda-makearray
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
rows | yes | none | scalar, range, array, reference | once |
columns | yes | none | scalar, range, array, reference | once |
lambda | yes | none | scalar, range, array, reference | once |
Signature: lambda-isomitted
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
value | yes | none | scalar, range, array, reference | once |
Signature: groupby-groupby
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
rowFields | yes | none | scalar, range, array, reference | once |
values | yes | none | scalar, range, array, reference | once |
function | yes | none | scalar, range, array, reference | once |
fieldHeaders | no | none | scalar, range, array, reference | once |
totalDepth | no | none | scalar, range, array, reference | once |
sortOrder | no | none | scalar, range, array, reference | once |
filterArray | no | none | scalar, range, array, reference | once |
fieldRelationship | no | none | scalar, range, array, reference | once |
Signature: groupby-pivotby
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
rowFields | yes | none | scalar, range, array, reference | once |
colFields | yes | none | scalar, range, array, reference | once |
values | yes | none | scalar, range, array, reference | once |
function | yes | none | scalar, range, array, reference | once |
fieldHeaders | no | none | scalar, range, array, reference | once |
rowTotalDepth | no | none | scalar, range, array, reference | once |
rowSortOrder | no | none | scalar, range, array, reference | once |
colTotalDepth | no | none | scalar, range, array, reference | once |
colSortOrder | no | none | scalar, range, array, reference | once |
filterArray | no | none | scalar, range, array, reference | once |
relativeTo | no | none | scalar, range, array, reference | once |
Signature: groupby-percentof
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
dataSubset | yes | none | scalar, range, array, reference | once |
dataAll | yes | none | scalar, range, array, reference | once |
Semantic profiles
These values are normative for the listed Sheetwrite subset. function-defined and contextual are explicit limitations: consult the formula guide's function-specific sections rather than assuming another spreadsheet's edge behavior.
Semantics: scalar
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: contextual array: contextual broadcast: function-defined result: scalar |
| coercion | blank: zero text: number-if-parseable boolean: number error: propagate |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: binary64 domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: aggregate
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: accepted broadcast: none result: scalar |
| coercion | blank: ignored text: ignored-in-ranges boolean: ignored-in-ranges error: propagate |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: binary64 domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: information
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: contextual array: contextual broadcast: function-defined result: scalar |
| coercion | blank: preserved text: preserved boolean: preserved error: function-defined |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: exact domain: all-inputs iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: logical
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: contextual broadcast: function-defined result: scalar |
| coercion | blank: false text: function-defined boolean: preserved error: propagate |
| text | case: insensitive wildcard: literal |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: exact domain: all-inputs iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: lazy-control
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: contextual array: contextual broadcast: function-defined result: contextual |
| coercion | blank: function-defined text: function-defined boolean: function-defined error: trap-selected |
| text | case: insensitive wildcard: literal |
| environment | locale: invariant dateSystem: function-defined |
| numeric | tolerance: function-defined domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: contextual lazy: lazy-branches spill: contextual fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: let-binding
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: accepted broadcast: function-defined result: contextual |
| coercion | blank: preserved text: preserved boolean: preserved error: propagate |
| text | case: insensitive wildcard: literal |
| environment | locale: invariant dateSystem: function-defined |
| numeric | tolerance: function-defined domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: contextual lazy: lazy-bindings spill: contextual fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: text
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: contextual array: contextual broadcast: function-defined result: scalar |
| coercion | blank: empty-text text: preserved boolean: preserved error: propagate |
| text | case: function-defined wildcard: literal |
| environment | locale: function-defined dateSystem: function-defined |
| numeric | tolerance: not-applicable domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: text-sensitive
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: contextual array: contextual broadcast: function-defined result: scalar |
| coercion | blank: empty-text text: preserved boolean: preserved error: propagate |
| text | case: sensitive wildcard: literal |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: not-applicable domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: text-search
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: contextual array: contextual broadcast: function-defined result: scalar |
| coercion | blank: empty-text text: preserved boolean: preserved error: propagate |
| text | case: insensitive wildcard: supported |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: not-applicable domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: criteria
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: accepted broadcast: pairwise result: scalar |
| coercion | blank: function-defined text: function-defined boolean: function-defined error: propagate |
| text | case: insensitive wildcard: supported |
| environment | locale: invariant dateSystem: function-defined |
| numeric | tolerance: binary64 domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: date-time
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: contextual array: contextual broadcast: function-defined result: scalar |
| coercion | blank: zero text: number-if-parseable boolean: number error: propagate |
| text | case: insensitive wildcard: literal |
| environment | locale: invariant dateSystem: excel-1900 |
| numeric | tolerance: binary64 domain: bounded iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: volatile-date
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: rejected array: rejected broadcast: none result: scalar |
| coercion | blank: preserved text: preserved boolean: preserved error: propagate |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: not-applicable dateSystem: host-clock |
| numeric | tolerance: binary64 domain: bounded iteration: kind: none maximum: none |
| calculation | dependencies: none lazy: not-applicable spill: scalar fill: source-preserved copy: source-preserved structuralRewrite: source-preserved |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: lookup
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: accepted broadcast: function-defined result: contextual |
| coercion | blank: function-defined text: function-defined boolean: function-defined error: propagate |
| text | case: insensitive wildcard: function-defined |
| environment | locale: invariant dateSystem: function-defined |
| numeric | tolerance: exact domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: contextual fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: array
| Dimension | Contract |
|---|---|
| shape | scalar: contextual range: accepted array: accepted broadcast: function-defined result: array |
| coercion | blank: preserved text: preserved boolean: preserved error: propagate |
| text | case: function-defined wildcard: function-defined |
| environment | locale: invariant dateSystem: function-defined |
| numeric | tolerance: function-defined domain: bounded iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: array fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: financial
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: contextual array: contextual broadcast: function-defined result: scalar |
| coercion | blank: zero text: number-if-parseable boolean: number error: propagate |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: binary64 domain: bounded iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: financial-iterative
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: accepted broadcast: none result: scalar |
| coercion | blank: zero text: number-if-parseable boolean: number error: propagate |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: binary64 domain: bounded iteration: kind: bounded maximum: 100 |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: analysis-distributions
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: contextual array: contextual broadcast: none result: scalar |
| coercion | blank: zero text: number-if-parseable boolean: number error: propagate |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: binary64 domain: bounded iteration: kind: function-defined maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: analysis-descriptive
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: accepted broadcast: none result: scalar |
| coercion | blank: ignored text: ignored-in-ranges boolean: ignored-in-ranges error: propagate |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: binary64 domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: analysis-regression
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: accepted broadcast: none result: contextual |
| coercion | blank: function-defined text: function-defined boolean: function-defined error: propagate |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: binary64 domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: contextual fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: analysis-math
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: accepted broadcast: none result: contextual |
| coercion | blank: function-defined text: function-defined boolean: function-defined error: function-defined |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: binary64 domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: contextual fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: analysis-database
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: accepted broadcast: none result: scalar |
| coercion | blank: function-defined text: function-defined boolean: function-defined error: function-defined |
| text | case: insensitive wildcard: supported |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: binary64 domain: function-defined iteration: kind: none maximum: none |
| calculation | dependencies: tracked lazy: eager spill: scalar fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: analysis-lambda
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: accepted broadcast: none result: contextual |
| coercion | blank: function-defined text: function-defined boolean: function-defined error: propagate |
| text | case: not-applicable wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: binary64 domain: function-defined iteration: kind: bounded maximum: 2000000 |
| calculation | dependencies: tracked lazy: function-defined spill: contextual fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Semantics: analysis-groupby
| Dimension | Contract |
|---|---|
| shape | scalar: accepted range: accepted array: accepted broadcast: none result: contextual |
| coercion | blank: function-defined text: function-defined boolean: function-defined error: propagate |
| text | case: insensitive wildcard: not-applicable |
| environment | locale: invariant dateSystem: not-applicable |
| numeric | tolerance: binary64 domain: function-defined iteration: kind: bounded maximum: 2000000 |
| calculation | dependencies: tracked lazy: function-defined spill: contextual fill: relative-reference-rewrite copy: relative-reference-rewrite structuralRewrite: ast-reference-rewrite |
| persistence | snapshot: formula-source history: formula-source collaboration: formula-source xlsxSource: rewrite-on-structural-edit |
Dialect profiles
Dialect: regex-lite-subset
| Dialect | Status |
|---|---|
| Microsoft Excel | divergent |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: REGEXTEST, REGEXEXTRACT, and REGEXREPLACE use the regex-lite syntax, not full PCRE2. Lookaround, backreferences, Unicode classes such as \p{L}, and other patterns the engine cannot compile return #VALUE!. \d, \s, and \w use ASCII character classes. \b uses ASCII word boundaries. As in Excel, caseSensitivity is 0 (case-sensitive, the default) or 1 (case-insensitive). Case-insensitive matching and the (?i) flag fold ASCII letters only. If caseSensitivity is 1 or the pattern enables an inline i flag, a pattern with a non-ASCII cased letter or a \x, \u, or \U escape returns #VALUE!. Regex programs are limited to 2 MiB. Text inputs and outputs are limited to 32,767 characters.
Dialect: excel-documented
| Dialect | Status |
|---|---|
| Microsoft Excel | documented |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: Google Sheets and OpenFormula results require producer evidence before compatibility is claimed.
Dialect: analysis-distributions
| Dialect | Status |
|---|---|
| Microsoft Excel | documented |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: Available only in the optional @sheetwrite/formulas analysis build; default @sheetwrite/wasm returns #NAME?. Scalar binary64 evaluation, not array broadcasting or blanket spreadsheet parity. Domain validation and finite numerical convergence may return #NUM!.
Dialect: analysis-descriptive
| Dialect | Status |
|---|---|
| Microsoft Excel | documented |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: Available only in the optional @sheetwrite/formulas analysis build; default @sheetwrite/wasm returns #NAME?. Regression functions skip pairs where either value is not a number. PERCENTRANK results are truncated, not rounded, to the requested significant digits.
Dialect: analysis-regression
| Dialect | Status |
|---|---|
| Microsoft Excel | documented |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: Available only in the optional @sheetwrite/formulas analysis build; default @sheetwrite/wasm returns #NAME?.
Dialect: analysis-math
| Dialect | Status |
|---|---|
| Microsoft Excel | documented |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: Available only in the optional @sheetwrite/formulas analysis build; default @sheetwrite/wasm returns #NAME?. Hidden rows are not part of the engine model. AGGREGATE row visibility options therefore have no effect. Matrix functions use engine resource limits.
Dialect: analysis-database
| Dialect | Status |
|---|---|
| Microsoft Excel | documented |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: Available only in the optional @sheetwrite/formulas analysis build; default @sheetwrite/wasm returns #NAME?. Database criteria use header names, AND across columns, and OR across rows. Custom formula predicates under headers that are not database fields are not supported.
Dialect: analysis-lambda
| Dialect | Status |
|---|---|
| Microsoft Excel | documented |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: Available only in @sheetwrite/formulas. Named ranges are supported, but workbook-defined named formulas are not available. LAMBDA uses lexical parameters and LET bindings. Calls are limited to depth 64 and 2000000 work units. Helpers reject nested arrays.
Dialect: analysis-groupby
| Dialect | Status |
|---|---|
| Microsoft Excel | documented |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: Available only in @sheetwrite/formulas. Aggregators must be a single eta-reduced built-in or a LAMBDA with one or two parameters. Vectors of functions are not supported. Grouping and output use the engine spill and work limits.
Implementation profiles
Implementation: text-analysis
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/eval/analysis/mod.rs |
| Evaluator | implementedpackages/wasm/src/eval/analysis/text.rs |
| Formula assist | implementedpackages/wasm/src/eval/analysis/mod.rs |
| Evidence | behavior-testedpackages/wasm/src/eval/analysis/text.rs |
Implementation: implemented-assisted
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/mod.rspackages/wasm/src/eval/functions.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedpackages/wasm/src/tests.rspackages/core/test/formula-assist.test.ts |
Implementation: parser-assisted-evaluator-declared
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | declared |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedpackages/core/src/formula-assist.ts |
Implementation: parser-declared-target
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | declared |
| Formula assist | missingpackages/core/src/formula-assist.ts |
| Evidence | registration-testedscripts/formula-contract.test.ts |
Implementation: analysis-distributions
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/analysis/distributions.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedscripts/formula-contract.test.ts |
Implementation: analysis-descriptive
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/analysis/descriptive.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedscripts/formula-contract.test.ts |
Implementation: analysis-dates
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/analysis/dates.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedscripts/formula-contract.test.ts |
Implementation: analysis-finance
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/analysis/finance.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedscripts/formula-contract.test.ts |
Implementation: analysis-regression
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/analysis/regression.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedpackages/wasm/src/eval/analysis/regression.rsscripts/formula-contract.test.ts |
Implementation: analysis-math
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/analysis/math.rspackages/wasm/src/eval/analysis/aggregate.rspackages/wasm/src/eval/analysis/matrices.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedscripts/formula-contract.test.ts |
Implementation: analysis-database
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/analysis/database.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedscripts/formula-contract.test.ts |
Implementation: analysis-reshape
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/analysis/reshape.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedpackages/wasm/src/eval/analysis/reshape.rs |
Implementation: analysis-lambda
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/analysis/lambda.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedpackages/wasm/src/eval/analysis/lambda.rs |
Implementation: analysis-groupby
| Layer | Status and source/evidence |
|---|---|
| Parser | implementedpackages/wasm/src/calc.rs |
| Evaluator | implementedpackages/wasm/src/eval/analysis/groupby.rs |
| Formula assist | implementedpackages/core/src/formula-assist.ts |
| Evidence | source-linkedpackages/wasm/src/eval/analysis/groupby.rs |
Unsupported categories
Unknown functions retain their source and evaluate to #NAME?; Sheetwrite does not silently execute a network, custom-code, or compatibility fallback.
| Category | Scope | Examples | Source | Evidence |
|---|---|---|---|---|
Volatile recalculation (volatile) | Automatic volatility semantics beyond the existing clock functions are outside the supported contract. | INDIRECT, OFFSET, RAND, RANDBETWEEN, RANDARRAY | Math and trigonometry functions | scripts/formula-contract.test.ts |
Network functions (network) | Formula evaluation never performs network requests. | ENCODEURL, FILTERXML, IMAGE, WEBSERVICE | Web functions | scripts/formula-contract.test.ts |
External data functions (external) | Functions that query external providers or live data connections are not evaluated. | GOOGLEFINANCE, IMPORTDATA, IMPORTHTML, IMPORTXML, RTD | Add-in and Automation functions | scripts/formula-contract.test.ts |
Cube functions (cube) | OLAP cube members, sets, and values are not resolved. | CUBEMEMBER, CUBESET, CUBEVALUE | Cube functions | scripts/formula-contract.test.ts |
Arbitrary external workbook references (external-workbook) | References to arbitrary workbook files are not loaded or dereferenced. | '[Book.xlsx]Sheet1'!A1 | Lookup and reference functions | scripts/formula-contract.test.ts |
In particular, automatic volatile functions beyond the explicit host-clock barrier, network/external-data functions, arbitrary external workbook links, database functions, cube/OLAP functions, and LAMBDA/higher-order execution are unsupported. TODAY and NOW are the documented clock-function exception; LET is supported and is not a LAMBDA fallback.