How to create recursive functions in Excel with LAMBDA

Recursive function is a term for describing behavior of a function that calls itself from within its own code. This is necessary for solving a problem where the solution relies on the results from multiple instances of the same problem. In this guide, we're going to show you how to create recursive functions in Excel.

Download Workbook

LAMBDA Function

Before the LAMBDA function, the only option for creating recursive calculations in Excel was using VBA. VBA allows you to create your own user defined functions. However, VBA requires some coding knowledge.

With the new LAMBDA function, you can create your own functions using only Excel formulas. Briefly, the LAMBDA function is a special function that you can use in a named range, and it allows you to use that named range as a function. It also supports recursive calculations.

To create a user defined function with LAMBDA, follow the steps below:

1. Open the New Name dialog by following Formulas > Define Name path in the Ribbon.
2. Type in a friendly name for your formula. To call the formula recursively, you should use this name inside your formula. For example, “MyFormula”.
3. Enter the LAMBDA formula e.g., =LAMDBA(x,y,x+y)
4. Click the OK button to create your user defined function.
   
5. Once the named range is saved, you can use it just like any other formula.
   

Let us briefly explain how the LAMBDA function works. The LAMBDA function’s last argument should always be the formula itself. The arguments before the formula are the arguments which will be used in the formula.

For example, the x+y function needs 2 arguments: x and y. Thus, the first arguments in the LAMBDA function are x and y. The last argument is the formula that uses these arguments.

As of writing this article, the LAMBDA function is only available to Office 365 users.

Creating recursive functions with LAMBDA

Fibonacci sequence

Let’s see the LAMBDA function on an example. Our first example is about the Fibonacci sequence, which is a sequence of numbers where each number is the sum of the two preceding values, starting from 0 and 1.

The function below returns the nth number in a Fibonacci sequence.

The function of n depends on same function’s instances for n-1 and n-2. Thus, the function needs to be recalculated until n is equal to 2, and F(0) and F(1) return 0 and 1 respectively.

With the help of the LAMBDA function we can create this function like this:

Note that we have named our function “Fib”. As a result, the function can be recalled as Fib(n-1) and Fib(n-2) in the same function. Remember to change this if your formula has a different name.

Ackermann Function and recursion

The Ackermann function, named after Wilhelm Ackermann, is a multi-variable function from natural numbers to natural numbers with a very fast rate of growth. It is accepted one of the simplest examples of a function that is computable but not primitive recursive.

Here is the equation:

If m and n values are not equal to 0 (third line), the function calls itself to calculate its second argument. The called function will call another, until the conditions in the first two rows are met.

The following is the Excel version of the Ackermann Function. We named it “Ack”.

Remove unwanted characters with a recursive function

The LAMBDA function can also be used to remove a set characters from strings. You can use functions like SUBSTITUTE and REPLACE to do this, but both functions can work with an entire string value, and not specific characters.

The RemoveCharacters function replaces each character with a an empty string (“”) starting from the left. It sends the lastly calculated character set without the first character each time it runs, until there are no characters left in the set.