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
Enter your email address below to download the sample 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:
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:
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)))
And you can define it as a name in the name manager.
I had trouble getting your formula to work properly.
FACTORIAL, LAMBDA(ME, n, IF(x<2,1,n*ME(ME,n-1))),
It contains an undefined variable x, which should be replaced by n. It also needs to pass FORMULA into the internal Lambda as another parameter:
FACTORIAL, LAMBDA(ME,n, IF(n<2,1,n*ME(ME,n-1))),
Oops, thanks for spotting, Jon! I did not copy that from my file to my blog post very well at all. I’ve fixed it now.
Can you please help me with lambda wherein I require average of highest n values from given row?
I tried, but not getting answer
As I said before, please post your question and sample Excel file on our forum where we can help you further and you can clearly illustrate why you think you need a LAMBDA for this: https://www.myonlinetraininghub.com/excel-forum
Excellent post Mynda. I think LAMBDA and LET are two incredible improvements in Excel but will take a while to understand fully, and this post helps towards that. Recursion is difficult to visualise and this post goes a long way towards helping in the understanding of recursion in LAMBDA.
So pleased you found it helpful, Mike! Sure is a lot to learn with the new functions. Have fun with them 🙂