A new feature Excel has recently rolled out is the ability to create Lambda functions. In short, this is a way to create custom functions within Excel that utilize variables. Previously, you would have needed to use VBA to accomplish this. But as long as you’re running Microsoft 365 and have the latest version of Excel, you can do that yourself. Here’s a quick overview of how it works.
Creating a simple Lambda function
Similar to how you might create named ranges, you can easily create a new Lambda function within the name manager in Excel. Click on the Formulas tab and then click on the Name Manager button:
Then, click on the New button, which will then give you the following dialog box:
I’ll set the name equal to times5 because what I want to do is take a value and multiply it by 5. In a Lambda function, I need to specify any variables, and then, what I want the function to do. I’ll use x as my variable and multiply it by 5 in this formula:
I’ll plug that into the named range so that it is filled out as follows:
When I click OK, the function is created. Now I can go into Excel and reference the function by using its name:
That will return a value of 50 as it will take the x value (10) and multiply it by 5.
Creating a Lambda function with multiple variables
Let’s move on to a more complex function. This time, I will calculate the compounded annual growth rate. For this, I will need a percent change and the number of years over which it will happen. Let’s say an investment grew by 200% over a period of 10 years. I want to know how much it would have averaged each year to compound to such a return. The formula to calculate compounded annual growth rate is as follows:
(1 + percent change) ^ ( 1 / # of Years) - 1
The calculation in this case ends up being 3^(1/10)-1 which returns a value of 0.116, or 11.6% after converting the value into a percentage.
To convert this into a function, I’ll set the percent change as x and the # of years as y:
The first two arguments are variables as there is no calculation taking place. But the Lambda function recognizes that what follows afterward is the actual computation that needs to happen. I’ll create this as another named range, using CAGR as the name of this function:
Now, to reference this within Excel, I’ll call the function as follows:
Where 2 refers to the percent change (200%) and 10 is the number of years. This returns the same value of 11.6% as when doing it manually through the calculation. The benefit of using a Lambda function is that it can easily be reused within the workbook without having to regenerate the formula over and over again.
If you liked this post on What Are Lambda Functions, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.