# Working with ME to test Excel's new LAMBDA function

Our Excel MVP plays with triangle numbers to illustrate how to address a recursion problem.Last month, we introduced you to **LAMBDA**, a function available in Office 365 Beta that revolutionises formula-building in Excel.

As you may recall, **LAMBDA** allows you to take any formula you’ve built in Excel, wrap it up in a **LAMBDA** function, and give it a name (eg, “**CUSTOM1**”). Once you’ve created the custom function, you can reuse it anywhere in your Excel workbook simply by referring to its name (eg, “**CUSTOM1”**).

**LAMBDA** “completes” Excel because it eliminates the application’s aversion to recursion. If you create a **LAMBDA** called **CUSTOM1**, you can call **CUSTOM1** within the definition of **CUSTOM1**. This is recursion, something that previously could not be done in Excel without using VBA or JavaScript.

## LAMBDA’s aversion to recursion

Wait, didn’t we just say that **LAMBDA** enables recursion in Excel? Yes, but I couldn’t resist that great subheading. Also, the subheading is accurate in that when you use recursion in a **LAMBDA**, it may not give the expected result. Instead, it will most likely generate an error, making you think that the expression you have written is incorrect.

Let me illustrate with a simple example (**download the Excel file** to follow along). Consider the following.

In cell G12, I have typed the number nine (9), which generates the list of numbers one (1) through nine (9) from cell G18** **downward, using the **SEQUENCE **function, eg:

**=SEQUENCE(G12)**

For those who recognise this is a dynamic array function and that you require Office 365 at this juncture, you are quite right — but I need the Office 365 Beta version for the **LAMBDA **functions, so it’s OK. I apologise if you don’t have Office 365, but again, you might wish to reconsider which version of Excel and Office you are running.

Back to the example, cell G16 simply sums the list created by the **SEQUENCE** function:

**=SUM(G18#)**

Therefore, inputting the numbers one (1) through nine (9) in cell G12 would generate the following results in cell G16: 1, 3, 6, 10, 15, 21, 28, 36, 45, …

These numbers are known as the **triangle numbers** in mathematics. You can see why in the image below.

You can generate this sequence using a **LAMBDA**. Now, yes, I know the formula above works and you can even use the algebraic solution:

**=G12*(G12+1)/2**

too, but the point is that I want to show you how to create a recursive **LAMBDA **function that is simple to follow.

On the Ribbon, go to the **Formulas** tab and click on **Name Manager** (Ctrl+F3) and click on the **New…** button.

I have named my **LAMBDA** function **Triangle**, and the reference (“Refers to:”) is given by

**=LAMBDA(x, IF(x<2, 1, x + Triangle(x-1)))**

Here, the **LAMBDA** function takes a parameter **x** and defines it as one (1) if it is less than two (2), else it takes the value **x** and adds the **LAMBDA** value for **x-1 **— hence the recursion. This **LAMBDA** function has been named **Triangle **(in the **Name:** box) and is referred to in the formula, too.

Therefore, for **x **equals nine (9):

**Triangle(9)** = 9 + **Triangle(8)
** = 9 + 8 +

**Triangle (7)**

= 9 + 8 + 7 +

**Triangle (6)**

= 9 + 8 + 7 + 6 +

**Triangle(5)**

= 9 + 8 + 7 + 6 + 5 +

**Triangle(4)**

= 9 + 8 + 7 + 6 + 5 + 4 +

**Triangle(3)**

= 9 + 8 + 7 + 6 + 5 + 4 + 3 +

**Triangle(2)**

= 9 + 8 + 7 + 6 + 5 + 4 + 3 + 2 +

**Triangle(1)**

= 9 + 8 + 7 + 6 + 5 + 4 + 3 + 2 + 1

= 45

Drawn out longhand, the recursion is clear.

This presupposes that **x** is an integer, and I have ensured this by incorporating data validation (**Data -> Data Validation** or Alt+D+L) into my input cell (keep it simple!):

Now that the **LAMBDA** function **Triangle **has been defined, the formula in Excel is easy:

I have purposely created a simple recursive formula to demonstrate how such a calculation might work. In reality, recursive formulas are likely to be much more sophisticated and/or complex.

Let’s assume that wasn’t the case, though, and you wanted to check whether your formula was working. Not everyone (anyone?) can type a formula and have it work the first time, every time. Many of us would want to try the formula in a cell first:

Oh, dear. That didn’t work. Of course it doesn’t. As explained above, it needs the parameter (**x**) to be defined:

Rats. This is the problem I alluded to earlier. My definition of the function refers to itself (ie,* *recursion is exhibited). The name has not yet been defined.

Now *watch out *here. Note the function here is **Triangle1**, not **Triangle**. There is a very important distinction. If I use **Triangle**, a similar formula *will* work:

This is very easy to explain. I have already defined **Triangle **in the **Name Manager**. This is why I am using **Triangle1**, to avoid this classic *gotcha*.

OK, let’s define **Triangle1 **then. That will mean wrapping the expression in a **LET **function. This allows you to stop writing the same expressions time and time again in a formula, or, as Microsoft puts it, it’s “… names on a formula level”.

This has not worked either, even though **Triangle1** has supposedly been cited by **LET**. The problem is **Triangle1** has not been defined when we are creating the **LAMBDA**. This is where our trick comes in — which also explains the title for this article: It’s all about **ME**. Let’s add a parameter (**ME**) to **Triangle1** and replace the recursive call to **Triangle1** with **ME **(ensuring you pass in **ME** as the first parameter):

Believe it or not, we are getting somewhere now. Even though **#NAME?** has been replaced with **#VALUE!**, the formula has evolved. Assuming you have background error checking enabled (**File -> Options -> Formulas -> Enable background error checking**), you can click on the error to see the issue:

We have an incorrect number of parameters. We have added a parameter to **Triangle1** (namely, **ME**), so the final argument of **LET **(**Triangle1($G$12)**) should also have two parameters. We do this by getting **Triangle1** to refer to itself, as shown below*.*

It works! We have checked and debugged our **LAMBDA** in Excel. We started by calling **Triangle1(Triangle1,$G$12)**, and when we evaluate that LAMBDA, we end up calling **Triangle1(Triangle1,$G$12-1)**,* *etc.

This technique has employed the **ME** parameter: By passing **Triangle1 **as a parameter to itself, it can then use that parameter to call itself.

Once you've got your head around this, proved for yourself Einstein’s Theory of General Relativity, and demonstrated Schwarzschild’s solution to Einstein’s field equations using tensor analysis, you have probably got your **LAMBDA** working properly. Now, simply remove all references to **ME**. Exclude **ME** as the first parameter and replace the **ME(** calls with the defined name you want to use (here, **Triangle1)**. Therefore, in this instance,

**=LET(Triangle1, LAMBDA(ME, x, IF(x<2, 1, x + ME(ME, x – 1))), Triangle1(Triangle1, $G$12))**

becomes

**=LAMBDA(x, IF(x<2, 1, x + Triangle1(x – 1)))**

ie, we have proved our expression is correct. We can now add this to the **Name Manager**.

## Word to the wise

Some common problems faced in financial modelling are *not* recursive in nature, so try not to confuse the issue unnecessarily. For example, the most common financial modelling illustration is the calculation of interest on an average cash balance. This is actually an instance of solving two simultaneous equations. Applying recursive logic would be inappropriate.

*— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of *Introduction to Financial Modelling

*. Send ideas for future Excel-related articles to him at*

*liam.bastick@sumproduct.com*

*. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an*FM

*magazine senior editor, at*

*Jeff.Drew@aicpa-cima.com*

*.*