# Excel 2024: Store Complex Formula Logic in LAMBDA function

October 03, 2024 - by Bill Jelen

Excel has a great formula language with hundreds of built-in calculations, but it doesn't have functions for every possible calculation. A new research project from Microsoft's Calc Intelligence team in England will allow you to create your own functions.

The new function, `LAMBDA`

, is available for any Microsoft 365 subscribers who opt into the Office Insiders program's Beta Channel. It's named after Princeton University mathematician Alonzo Church, who invented lambda calculus in 1936. Church was the doctoral advisor of Alan Turing, the famous mathematician who proposed the Turing machine to perform calculations long before modern computers were invented.

Imagine performing a complex calculation and passing variables to it. The variable names and logic are stored in the `LAMBDA`

function and after testing in the worksheet you create a Name and insert the logic into name manager.

In the beta versions of `LAMBDA`

, you were required to add `LAMBDA`

functions to the Name Manager in Excel. This tiny dialog box with a single-row text box for entering the formula was not a fun place to try to edit your formulas.

In early 2021, Microsoft Research released an Advanced Formula Environment add-in that makes LAMBDA easier to use. Here is how to install the Advanced Formula Editor.

1. From the Insert tab in Excel, go to Add-ins, Get Add-Ins. The Office Add-Ins dialog opens.

2. In the Search box, type Advanced Formula Environment and press Enter. The top result should be "Advanced formula environment, a Microsoft Garage project".

3. Click the Add button. Click the Continue button. The Advanced Formula Environment will appear on the right side of the Home tab.

There are three main buttons in the add-in. Click the + icon to define a new `LAMBDA`

. Click the Sync button to save your LAMBDAs to the Name Manager. Click the Import button to import from a GitHub Gist URL.

Let's start with a simple example. In a right triangle, the length of the hypotenuse is the square root of A squared plus B squared. To calculate the hypotenuse, you would use a formula such as `=LAMBDA(A,B,SQRT(A^2+B^2))`

.

In this formula, the first two arguments are variables to hold values that will be passed to the function. The final argument is the logic to perform the calculation. You aren't limited to just two input variables. Everything up to the last argument is considered to be a variable. The final argument always holds the calculation logic.

In the Advanced Formula Editor, click the + icon and choose Function. A box will appear where you name your `LAMBDA`

. A second box lets you edit your `LAMBDA`

.

Give this a name of `HYP`

. Type the formula `=LAMBDA(a, b, SQRT(a^2 + b^2))`

. Click the Add button to add the formula definition. Caution: The formula is not yet available to use. You must first click the Sync button to Sync Names With The Excel Name Manager.

After you sync the new `LAMBDA`

to the Name Manager, you can use the formula in the Excel grid. Excel offers intellisense, just like any other Excel function.

Here is the formula in the AFE panel:

The AFE panel is really just a conduit for getting the formula into the Name Manager:

Here are the formulas working in the grid. Notice that the intellisense even shows that the function is expecting two arguments, named a & b.

Before the AFE was available, you would sometimes want to edit and test the formula in the grid before trying to add it to the Name Manager. You could type the formula in the grid, followed by the argument values in parentheses. This still works. `=LAMBDA(A,B,SQRT(A^2+B^2))(3,4)`

would return the answer of 5.

If you need to change the logic in the future, edit the formula in the Name Manager or the Advanced Formula Environment.

Bonus: Importing LAMBDAs from GitHub

The AFE lets you import `LAMBDA`

from a GitHub Gist (pronouced jist). Chris Gross, from the Excel team, made this Gist available on the day the AFE was released. Import it and you will instantly have several LAMBDAs available in the workbook.

1. In the Advanced Formula Environment, click the Import icon.

2. A box appears where you can type a URL. Enter the following: https://gist.github.com/chgrossMSFT/d172fd552cf6893bcdc7942223cb0e55

3. Optionally, you can add these LAMBDAs to a Name Space. If you use a name space of MSFT, then all of the LAMBDAs imported will start with MSFT. This might help you to keep your `LAMBDA`

formulas categorized.

4. After a successful import, you can browse the new LAMBDAs. They won't be available to use until you press the Sync button to add them to the Name Manager.

By using a Name Space of MSFT, all of these functions will appear in the Formula AutoComplete when you type `=MSFT`

.

Here is the `MSFT.TEXTREVERSE`

function working in the grid.

Transferring LAMBDAs Between Workbooks

A `LAMBDA`

is stored in the Name Manager in the workbook. There is a "bug" in Excel that actually comes in very handy here. Let's say that you have a workbook with many LAMBDAs defined. Insert a new blank worksheet in that workbook. Copy the worksheet to another Excel file. All of the Names defined in the first workbook will be copied to the new workbook. This is usually pretty annoying. But in this, case it is a perfect way to transfer LAMBDAs from one workbook to another.

LAMBDAs Work in More Endpoints than VBA.

In the past, people might have used VBA to write their own user-defined functions. These were fine when everyone was using a PC or a Mac. But slowly, the small percentage of people using Excel on an iPad or Android or Excel Online is growing, and those versions of Excel can't support VBA. `LAMBDA`

functions, however, can be used with Excel online or a mobile device.

Charles Williams and the LAMBDA Explorer

MVP Charles Williams is working on an amazing free add-in called the LAMBDA EXPLORER. It allows you to watch your Lambda functions to calculate in slow motion. Download the beta from Excel Lambda Explorer. Recursive Lambdas are fully evaluated and can be expanded to the next recursion level: the arguments show at each level the progression of the recursion.

*This article is an excerpt from MrExcel 2024 Igniting Excel*