Lambda function lets users create custom made function right inside MS Excel. This is especially very useful while working with array operations that involve lengthy codes.
Microsoft announced the new Lambda() function that was made available to some of the Office insiders in early December 2020. This author was fortunate enough to get early access to it as part of initial users. In this blog, you would learn from his first-hand experience on what works and what are the challenges as well as some of the very useful new features he has created.
But first, let us understand a bit about the new Lambda function.
Lambda lets you create a custom function in MS Excel
This function helps you create a custom function using the name manager tool. The function can then be called back into your file directly. Unlike a user-defined function (UDF) created in VBA, this does not require the file to be saved as a macro-enabled file.
Let us see a small example of how to do that. Let us say you want to create a function to sum up the top 10 values in range B2:B2000. In office 365, you can do it by combining sum function, large function, and sequence function as follows:
=Sum(Large(B2:B2000,Sequence(10)))Of course, instead of typing the 10 inside the function, you can input it in a cell and link it to the same.
But as you can see that this function involves three nested functions. Instead, you can use the Lambda function to create an alternative. You can try these steps:
Go to Formulas tab and click on Name Manager command button. In the name manager box click on New
As shown in the image below, you can do the following.
First, give the name of the function in the “Name” section. I have given it as SumTopN
Then in the refers to section, I have used the Lambda function to define it.
You can specify all the parameters that need to be passed on to the function. The parameters we need to get the sum of top n numbers would be the range and how many top values we need.
The last part of the Lambda function shows you what to give as output. So, here I have given a formula very similar to what was shown above. However, the range and the n are not specific but it refers to the variable name mentioned inside the Lambda function.
Once we are done with that, we can directly call the function in our sheet.
=SumTopN(B2:B2000,10) and see it giving us the result.
Why should we use the Lambda function?
You might be wondering why one should first go about creating the function and then use it rather than directly doing that in an excel file. There are two reasons for it:
First, especially in the context of dynamic arrays, some of the functions you may create could be extremely complicated. If the same has to be done multiple times, it is better to create a custom function with Lambda.
For example, this is the formula I would use to create a running total (before Scan function was introduced).
=LET(Rng,Table1[Values],
CountX,COUNT(Rng),
SeqN,SEQUENCE(CountX),
Ns,TRANSPOSE(SeqN)-SeqN+1,
PosNs,IF(Ns<=0,0,1),
Output,MMULT(TRANSPOSE(PosNs*Rng),Sequence(CountX,1,1,0)),
Output)If we have to get the running total in multiple places, we have to give such a complex and lengthy code in so many places. And there is a very good chance we could make a mistake while reusing it. Further, a normal user would least likely be able to appreciate what the code does.
Instead, I could create a running total function like this in the name manager with the name
RunningTotal:
=Lambda(Rng, LET(CountX,COUNT(Rng), SeqN, SEQUENCE(CountX), Ns, TRANSPOSE(SeqN)-SeqN+1, PosNs, IF(Ns<=0,0,1), Output, MMULT(TRANSPOSE(PosNs*Rng), All1ColMatrx(CountX)), Output)I can then simply recall this using
=RunningTotal(B2:B2000)The second reason to use this feature is to make your actions more descriptive (credits at the end).
Let us say you have sales numbers in cell D4 and the cost of goods sold in D5. Gross profit would be calculated as D4 – D5. Now, someone who understands how to calculate gross profit would be able to understand what you are doing. However, others may not. Instead, we can create a function GrossProfit and call the value into the cell as GrossProfit(D4, D5). You could also name the ranges and use the formula GrossProfit(Sales, COGS)
Create your own library of Lambda Function!
If you know some of the most frequently used functions, you can create a file with all those functions. You can keep that as your base template and use the template every time you start on a new project.
We have also created a library of some of the most common functions that we have been using while working with a dynamic arrays. You can download the same from this link:
In this library, you would find a whole lot of functions including the following:
HJoinArray: Combine two arrays horizontally
VJoinArray: Combine two arrays vertically
RunningTotal: Calculate the running total of a list
SumTopN: Sum the top N numbers
Dice: Dice an array
And many more..
Work with recursions
Recursions refer to a situation where a program refers to itself. In this article, we have explained how to use recursions to calculate implied volatility based on the option premium.
But you need to be a little careful
As a user, one of the biggest challenges, I have observed working with Lambda function is a typographical error. In VBA, if a variable is misspelled, we can make the interpreter point that out. It would tell us exactly where the issue is.
However, we do not have that luxury while creating Lambda function. If we make a typographical error, it will throw a #NAME? error but it is not likely to point us the variable causing the mistake.
So, here is the process we need to follow:
1. Try the function logic in a cell. Taking example from the SumTopN function we did, try this formula in a cell and make sure it works:
=Sum(Large(B2:B2000,Sequence(10)))
2. Then wrap it in a Lambda function and try it in a cell. This is how you need to apply Lambda in a cell:
Lambda(InRng,N,Sum(Large(InRng,Sequence(N))))(B2:B2000,10)
Note that when you are using Lamda inside a cell, we call the function immediately by putting the input parameters after enclosing the Lambda function. We refer to this as a function call. This approach should only be used for testing the function.
3. Copy the Lambda function, excluding the function call part, and create a name.
Sources and credits
1. Credit to Craig Hatmaker for sharing the suggestion on using the descriptive function for calculating profits