LAMBDA and higher-order functions

LAMBDA, LET, MAP/REDUCE/BYROW/BYCOL/SCAN/MAKEARRAY, and GROUPBY/PIVOTBY

The formula engine ships Excel's full LAMBDA family: LAMBDA as both an IIFE and a value, LET for named bindings, the six standard higher-order functions (MAP, REDUCE, BYROW, BYCOL, SCAN, MAKEARRAY), and GROUPBY / PIVOTBY built on the same infrastructure. Dependency tracking through lambda bodies is automatic.

LAMBDA

Two shapes — both work:

IIFE (call inline):

=LAMBDA(x, x*x)(5)         → 25
=LAMBDA(a, b, a+b)(2, 3)   → 5

As a value (pass to a HOF or store on a named range):

=MAP(A1:A3, LAMBDA(v, v*2))

Internally a standalone LAMBDA(...) rewrites at compile time to __RNC_LAMBDA__("params", "body source"), which constructs a runtime lambda value {__isLambda, params, body}. HOFs unwrap that value and re-evaluate the body once per element with bindings substituted in.

A recursion guard caps depth at 256 to keep runaway lambdas from blowing the JS stack.

LET

Bind names to values inside a formula. Source-level expansion: each binding's value is inlined into the body before parse.

=LET(n, 5, n*2)                  → 10
=LET(a, 3, b, 4, a+b)            → 7
=LET(name, "World", "Hello "&name) → "Hello World"

Nested LET works:

=LET(x, 10, LET(y, x*2, y+1))    → 21

A LET-bound LAMBDA can be called like a function — the engine substitutes Function-shaped occurrences of binding names with the lambda source, then the IIFE inliner collapses it:

=LET(double, LAMBDA(x, x*2), double(5))    → 10
=LET(add, LAMBDA(a, b, a+b), add(3, 4))    → 7

Closures via LET capture

LET expands BEFORE LAMBDA, so any LET binding referenced inside a LAMBDA body is baked into the body source. This gives you closures for free:

=LET(n, 5, LAMBDA(x, x+n))
// becomes: __RNC_LAMBDA__("x", "x+(5)")

=LET(n, 10, LET(addn, LAMBDA(x, x+n), addn(5)))   → 15

Higher-order functions

MAP

Apply a lambda to each element of an array, returns a same-shape result:

=MAP(A1:A3, LAMBDA(v, v*2))
// [[1], [2], [3]] → [[2], [4], [6]]

Multiple input arrays of the same shape:

=MAP(A1:A3, B1:B3, LAMBDA(a, b, a+b))

REDUCE

Left fold:

=REDUCE(0, A1:A3, LAMBDA(acc, v, acc+v))    // sum
=REDUCE(1, A1:A3, LAMBDA(acc, v, acc*v))    // product

SCAN

Like REDUCE but returns intermediate accumulator values, same shape as input:

=SCAN(0, A1:A3, LAMBDA(acc, v, acc+v))      // running total
// [[1], [2], [3]] → [[1], [3], [6]]

BYROW / BYCOL

Apply a lambda to each row (or column) as a 1D array:

=BYROW(A1:C3, LAMBDA(row, SUM(row)))
=BYCOL(A1:C3, LAMBDA(col, AVERAGE(col)))

MAKEARRAY

Build an N×M array by invoking a lambda with (r, c) per cell (1-indexed per Excel):

=MAKEARRAY(2, 3, LAMBDA(r, c, r*10+c))
// [[11, 12, 13],
//  [21, 22, 23]]

GROUPBY

Aggregate rows by row-field tuple, calling a lambda once per group on the group's value vector:

=GROUPBY(A1:A6, B1:B6, LAMBDA(v, SUM(v)))

Args:

Arg	Purpose
row_fields	column or 2D — each row defines a group key tuple
values	column or 2D — values to aggregate per group
function	LAMBDA receiving the group's value vector (1D array) and returning a scalar
[field_headers]	0 (default) / 1 — echo the leading header row in output
[total_depth]	0 (default) / 1 grand-total at top / -1 grand-total at bottom
[sort_order]	1 (default, asc) / -1 (desc) by the first row-field column
[filter_array]	optional boolean column same length as row_fields; rows where FALSE are excluded

// Sum per category
=GROUPBY(A1:A6, B1:B6, LAMBDA(v, SUM(v)))

// Average per category, descending
=GROUPBY(A1:A6, B1:B6, LAMBDA(v, AVERAGE(v)), 0, 0, -1)

// With grand total at top
=GROUPBY(A1:A6, B1:B6, LAMBDA(v, SUM(v)), 0, 1)

// Count + filter
=GROUPBY(A1:A6, B1:B6, LAMBDA(v, COUNT(v)), 0, 0, 1, C1:C6)

The aggregation lambda receives the group's value vector as an Excel array literal ({a, b, c}) substituted into the body source — so any aggregator that accepts an array works (SUM, AVERAGE, COUNT, MAX, MIN, custom LAMBDAs, etc.).

PIVOTBY

Two-axis cousin of GROUPBY — adds a column axis on top of the row grouping:

=PIVOTBY(rows, cols, values, function,
         [row_headers], [row_total_depth], [row_sort_order],
         [col_headers], [col_total_depth], [col_sort_order],
         [filter_array])

// Rows = regions, columns = quarters, values summed
=PIVOTBY(A1:A8, B1:B8, C1:C8, LAMBDA(v, SUM(v)))

// With row + column grand totals at top/left
=PIVOTBY(A1:A8, B1:B8, C1:C8, LAMBDA(v, SUM(v)), 0, 1, 1, 0, 1, 1)

// Totals at bottom/right
=PIVOTBY(A1:A8, B1:B8, C1:C8, LAMBDA(v, SUM(v)), 0, -1, 1, 0, -1, 1)

// Sort both axes descending
=PIVOTBY(A1:A8, B1:B8, C1:C8, LAMBDA(v, SUM(v)), 0, 0, -1, 0, 0, -1)

Output is a 2D spilled array — a header row of distinct column-tuples, then one row per distinct row-tuple prefixed by row labels.

Dependency tracking through lambda bodies

When a lambda body references a cell, the dep parser walks the body string via a fresh inner parser instance and registers each ref as a precedent of the outer formula. Recalc propagates correctly:

=MAP(A1:A3, LAMBDA(v, v+B1))
// Both A1:A3 AND B1 are tracked as dependencies

=REDUCE(0, A1:A3, LAMBDA(acc, v, acc+v+C5))
// C5 is tracked

Named LAMBDAs (workbook scope)

Define a named range whose value is a LAMBDA and call it like a function:

// In Name Manager:
Commission = =LAMBDA(sales, IF(sales > 1000, sales*0.1, sales*0.05))

// Anywhere in the workbook:
=Commission(500)    → 25
=Commission(5000)   → 500
=MAP(A1:A10, Commission)

See Named ranges for the full story on workbook + sheet scoping.

What's not yet supported

Recursive LAMBDA — =LET(fact, LAMBDA(n, IF(n<=1, 1, n*fact(n-1))), fact(5)) doesn't work yet. Body-reparse at runtime loses the LET binding for fact. A runtime registry that survives per-call re-parse is the fix; deferred.

See docs/getting-started/excel-google-sheet-compatibility.md → "Phase 7" for the full status.