Recursive LAMBDA Functions

Mynda Treacy

March 4, 2021

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

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

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:

factorial example

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.

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

6 thoughts on “Recursive LAMBDA Functions”

  1. I had trouble getting your formula to work properly.

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

    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:

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

    Reply
  2. Hello Mynda,

    Can you please help me with lambda wherein I require average of highest n values from given row?

    I tried, but not getting answer

    =LAMBDA(range,n,
    IF(n=1,LARGE(range,n),
    AVERAGE(LARGE(range,rajan(n,n-1)))))(B2:F2,3)

    Reply
  3. 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.

    Reply

Leave a Comment