Loading documentation…
Loading documentation…
This page lists the 20 functions of the dynamic array family. Taxonomy/source: Lookup and reference functions. The formula function contract has every family, the engine builds, and the evaluation limits.
All functions
| Function (aliases) | Engine | Contract |
|---|---|---|
FILTER | Default + full | |
SORT | Default + full | |
UNIQUE | Default + full | |
TRANSPOSE | Default + full | |
SEQUENCE | Default + full | |
TAKE | Default + full | |
DROP | Default + full | |
CHOOSECOLS | Default + full | |
CHOOSEROWS | Default + full | |
EXPAND | Full only | |
HSTACK | Full only | |
SORTBY | Full only | |
TOCOL | Full only | |
TOROW | Full only | |
VSTACK | Full only | |
WRAPCOLS | Full only | |
WRAPROWS | Full only | |
GROUPBY | Full only | |
PIVOTBY | Full only | |
PERCENTOF | Full only |
Argument order, required/default state, accepted shapes, repetition, and return shape come directly from the inventory.
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 |
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 |
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 |
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
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 |
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 |
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
index | yes | none | scalar, array, reference | one-or-more |
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 |
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | scalar, range, array, reference | one-or-more |
Return shape: array.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
array | yes | none | range, array, reference | once |
byArrayAndOrder | yes | none | scalar, range, array, reference | paired |
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 |
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 |
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 |
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 |
Return shape: scalar.
| Argument | Required | Default | Accepts | Repetition |
|---|---|---|---|---|
dataSubset | yes | none | scalar, range, array, reference | once |
dataAll | yes | none | scalar, range, array, reference | once |
These values are normative for the listed Sheetwrite subset. function-defined and contextual are explicit limitations: consult the formula guides' function-specific sections rather than assuming another spreadsheet's edge behavior.
| 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 |
| 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 | Status |
|---|---|
| Microsoft Excel | documented |
| Google Sheets | unverified |
| OpenFormula | unverified |
Limitations: Google Sheets and OpenFormula results require producer evidence before compatibility is claimed.
| 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.
| 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 |
| 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 |
| 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 |