Working with ME to test Excel's new LAMBDA function

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.