Microsoft recently announced the availability of LAMBDA functions, a new feature to Excel that allows users to create custom functions within Excel's formula language, without requiring extra code:
LAMBDA allows you to define your own custom functions using Excel’s formula language. ... With LAMBDA, you can take any formula you’ve built in Excel and wrap it up in a LAMBDA function and give it a name (like “MYFUNCTION”). Then anywhere in your sheet, you can refer to MYFUNCTION, re-using that custom function throughout your sheet.
Why reusable custom functions matter
Put simply, this allows us to build formulas that are much easier to compose, understand, and use. I'll give an example: when I create a monthly cohort-based calculation of churn, I often create a set of rows to map to each months' new acquisitions, and then calculate churn using one (or multiple) standard churn curves to reflect a logarithmic decline in churn, so that I can assume that a user's churn rate goes down as a function of how long someone is a user. I create the formula so that it applies in all cases that occur in the set of cohort calcs, meaning that the formula is created, then copied over each cell, and for 3 years of monthly cohorts, that means 36 columns (3 years * 12 months per per) and 36 rows. With LAMBDA, instead I would create one custom function (that is likely fairly long and complicated), then create a formula in those cells that merely calls the custom function and passes in the relevant parameters. Less code exposed to you, easier to understand, easier to audit.
Here's a good walkthrough on how to use it:
Great video from Leila Gharani on using Excel LAMBDA. Leila highlights the ability to do recursion. #Excel #ExcelIsAPlatform @msexcel https://t.co/Dho7QI1WbY
— Brian Jones (@jones206) December 16, 2020
The process is straightforward:
- Use Name Manager to define a name using a LAMBDA formula as its definition
- Then use the custom function you created in your worksheet, passing in the parameters into the function
You can also type LAMBDAs directly into your worksheet and call them in line (useful for testing), but to reuse them throughout your worksheet you will need to define them in Name Manager.
Using recursion in LAMBDA functions
Another powerful benefit is recursion:
If you create a LAMBDA called MYFUNCTION for example, you can call MYFUNCTION within the definition of MYFUNCTION. This is something that before, was only possible in Excel through script (like VBA/JavaScript).
You may not have run across a situation that requires recursion, but I see it as a valuable way to reduce the use of helper functions and output and streamline outputs for complicated calculations. Details on use at Announcing LAMBDA.
Details on setup, paramters, technical restrictions, and examples, consult the LAMBDA function support page on Microsoft.
When will LAMBDA functions show up in Foresight models?
Personally, I'm looking forward to using LAMBDA and updating many of my models to simplify the formula construction. That said, users of Foresight models won't see them deployed until the capability is widely available. Currently, LAMBDA functions are only available to Microsoft Office 365 subscribers that are using the latest version of Microsoft Office, with access to the Beta channel.
Here's how to get Beta channel access through Office Insider →
Since I build models used by people across many versions of Excel, Google Sheets, and other spreadsheet programs, I try to minimize the use of constructions that are not available to users of different versions. Once LAMBDA functions are publicly released and available to all Office 365 subscribers, I'll consider how to deploy them in my models.
Questions, ask anytime.