Recursive LAMBDA Functions

The new Excel LAMBDA function that we looked at last week also enables us to write custom recursive LAMBDA functions. Recursion is simply when a routine calls itself. That said, it can be a bit mind bending, so in this tutorial we’ll look at an example that’s easy to follow. Once you understand the structure of the function you can apply it to more complex tasks.

Note: If you aren’t familiar with LAMBDA yet, be sure to see my first tutorial on LAMBDA.

Watch the Video

Download Workbook

Recursive LAMBDA Function Example

A common example used to explain recursion is the factorial function. A factorial function multiplies all whole numbers from our chosen number down to 1. For example, if our chosen number is 5 the formula would be:

= 5 x 4 x 3 x 2 x 1

Which equals 120.

Note: there is already a factorial function called FACT in Excel, so you would never write your own lambda for this calculation, but it’s a good way to demonstrate recursion, so go with me.

A factorial function can be written as:

n * factorial(n-1)

In other words, the result of factorial(n) can be calculated as n multiplied by the result of factorial(n-1). And the call for n-1 can recursively descend lower, and lower, till 1.

We can write this function which I’ll call FACTORIAL, with LAMBDA like so:

FACTORIAL: =LAMBDA(n, IF(n<2, 1, n*FACTORIAL(n-1)))

It’s that reference to itself that can be super confusing and prevents us being able to evaluate the function in a cell like we can with non-recursive lambdas.

A key component of writing recursive lambdas is to provide it with an opportunity to bail out of the recursive loop. In the formula above I’ve done this with IF. That is, when n gets to less than 2, n is simply 1 and the formula can stop evaluating there without moving on to the recursive part.

IMPORTANT: the bailout is always written at the start of the function.

Note: If n = 0 then factorial will return 1. It might seem odd that multiplying no numbers together results in 1, but if you follow it backwards from 3 you can see the pattern:

Source: https://www.mathsisfun.com/numbers/factorial.html

Testing Recursive LAMBDAS

With non-recursive lambdas we can simply enter the variables in parentheses after the function, and we saw this in the LAMBDA Function example I gave last week like this:

=LAMBDA(x,y, x+y)(2,3),

But doing this with a recursive lambda like so:

=LAMBDA(n, IF(n<2, 1, n*FACTORIAL(n-1)))(5),

Will return a #NAME! error because FACTORIAL isn’t defined as a name yet.

One option is to define the name for the recursive lambda and then call it in the cell. However, toing and froing between the name manager and the worksheet is tedious when troubleshooting.

Hopefully, the Excel team will come up with a better tool for authoring lambdas soon. In the meantime, the suggestion from Microsoft is to use LET and a ME parameter. Using the FACTORIAL example, we can replace the recursive call to FACTORIAL with ME (passing in ME as the first parameter) like so:

=LET(

FACTORIAL, LAMBDA(ME, n, IF(n<2,1,n*ME(ME,n-1))),

FACTORIAL(FACTORIAL,5)

)

The ME parameter makes this possible by passing FACTORIAL as a parameter to itself, allowing it to then use that parameter to call itself.

Once you’re happy your lambda is returning the correct result you can remove the first ME parameter and replace the ME with the name you want to use, so

=LAMBDA(ME n, IF(n<2,1,n*ME(ME,n-1)))

Becomes:

=LAMBDA(n, IF(n<2,1,n*FACTORIAL(n-1)))

And you can define it as a name in the name manager.