sheets.wiki

LAMBDA recursion

2 min read

LAMBDA recursion allows the user to apply lambda terms recursively using anonymous recursion.

LET(
	func, LAMBDA(func, <variables>, <expression>),
	func(func, <variables>)
)
 
LAMBDA(func, func(func, <variables>))(LAMBDA(func, <variables>, <expression>))

Either syntax is valid; however, LET is typically easier to work with and read. In computer science terms, LET enables named function recursion.

Recursive formulae should have a base case. Iterative formulae, not to be confused with Iterative calculation, can be implemented using tail recursion. This is the most common use for LAMBDA recursion. However, tail call optimization is not guaranteed in Google Sheets — unlike many functional languages, tail-recursive formulas can still run into Calculation limits. Other forms of recursion, such as multiple recursion, are even more constrained for the same reason.

Example

Factorial is a standard illustration. Because Sheets does not guarantee tail call optimization, the call stack grows with input size, limiting this to small values before hitting Calculation limits:

=LET(
    fact, LAMBDA(self, n,
        IF(n <= 1, 1, n * self(self, n - 1))
    ),
    fact(fact, 5)
)
→ 120

Prefer REDUCE for iteration

For iterative tasks — accumulating a result across an array — REDUCE is almost always preferable to LAMBDA recursion. REDUCE is purpose-built for iteration, is more readable, and is not subject to the same stack growth. Reserve explicit recursion for problems that are inherently recursive in structure (e.g. tree traversal).

For cases requiring multiple accumulators in REDUCE, see LAMBDA data structures.

See Also

Backlinks