# Excel 2024: Combining Subformulas into a Single Lambda

October 07, 2024 - by Bill Jelen

Often, I will need a complicated formula. It is easier to build that formula in tiny steps. Once the subformulas are working, you can try to combine everything into one complicated formula.

Just as an example, your manager wants you to calculate sales per workday for this spreadsheet of sales in 2028.

As I start to think about this, I break it down into steps:

In the distant past, I would end up with one super-formula:

`=B4/NETWORKDAYS(DATEVALUE(A4&" 1, 2028"),EOMONTH(DATEVALUE(A4&" 1, 2028"),0))`

In the more recent past, I could re-use formula logic in the `LET`

function:

```
=LET(FirstOfMonth, DATEVALUE(A4&" 1, 2028"),
EndOfMonth, EOMONTH(FirstOfMonth,0),
NumberWorkDays, NETWORKDAYS(FirstOfMonth,EndOfMonth),
B4/NumberWorkDays)
```

But the new Import from Grid functionality in the Excel Labs add-in makes it simple to reduce the formula.

Start with a system of subformulas as shown here:

Select the input cell, the subformulas and the final result as shown above.

Install the Excel Labs add-in. Using the icon at the right side of the Home tab, open Excel Labs.

Click on the section for Advanced Formula Environment.

In the Excel Labs task pane, choose Modules and then Import from Grid.

The Excel Labs add-in will detect the output cell and the parameter cells.

Click Preview and you will see the formula being proposed.

Click Create. This adds the formula to the Advanced Formula Environment, but it is not available in the grid until you click the Save icon:

Clicking Save will add the Lambda function to the Name Manager in Excel. You can then use the formula as shown here:

Here is the final result after formatting the cell and copying the formula down.

The logic for Sales_Per_Day is stored in the Name Manager for the workbook. That means anyone who opens the workbook on any computer or in Excel Online will have access to this function. Copy the worksheet to a new workbook and the logic will travel to the new workbook. This is amazing.

Using LAMBDA with Data Types

The team that developed `LAMBDA`

is the same one that created data types and the `LET`

function. That means `LAMBDA`

was designed to work perfectly with Data Types. This enables more complex uses of `LAMBDA`

.

Say that you have city pairs in column A and B. You convert these to Geography data types using the Data Type gallery on the Data tab of the ribbon. The Map icons mean that each cell contains many fields about the city. Two of those fields are Latitude and Longitude.

There's a complicated formula for calculating the distance between two cities using their respective latitude and longitude. You start out assuming the radius of the Earth is 3,959 miles. The formula uses functions for arcsine and cosine and is generally one that most people would never remember: `=(3959*2)*ASIN(SQRT(0.5-COS((B2.Latitude-A2.Latitude)*PI()/180)/2+COS(A2.Latitude*PI()/180)*COS(B2.Latitude*PI()/180)*(1-COS((B2.Longitude-A2.Longitude)*PI()/180))/2))`

.

If you wrap that formula in the `LAMBDA`

function as `=LAMBDA(From,To,(3959*2)*ASIN(SQRT(0.5-COS((To.Latitude-From.Latitude)*PI()/180)/2+COS(From.Latitude*PI()/180)*COS(To.Latitude*PI()/180)*(1-COS((To.Longitude-From.Longitude)*PI()/180))/2)))`

and then store it in the Name Manager with an easy name like MILES, then you can calculate the distance between two cities using a formula as simple as `=MILES(A2,B2)`

.

To move that formula from one workbook to another, you simply copy any worksheet from the original workbook to a new workbook, and the names will travel along. With this portability, it's possible to envision a company's accounting department that has a library of custom `LAMBDA`

functions that move from workbook to workbook.

Note

Mathematicians are celebrating that the LAMBDA function makes Excel 'Turing Complete' and can solve any mathematical problem.

Bonus Tip: Perform a Loop with a Recursive LAMBDA

A `LAMBDA`

function can also call other `LAMBDA`

functions, and it can recursively call itself. This lets you create a `LAMBDA`

function that keeps calling itself until the task is complete.

These are likely the most complicated topic in the whole book. Let me demonstrate how one `LAMBDA`

can solve a problem that would be pretty difficult with regular Excel functions.

I frequently write articles for MrExcel.com. My full stack developer is Suat Ozgur. When Suat is converting my article to a web page, he puts the title in the URL to help with Search Engine Optimization. Converting a title from "Why I say 'Bazinga!' - my #1 Reason for Using `=INDEX(MATCH())!!`

", to a URL requires certain rules. The only characters that are used in our URLs are the lower case letters a through z and the digits 0 through 9 and a dash. As you scan through any article title, there are 37 characters that can be left alone. Another 26 upper case characters need to be converted to lower case. That leaves 192 other characters that have to be replaced with an dash. (Technically, Suat thinks he only has to check the 31 illegal characters that are on a U.S. keyboard. Because he thinks I am too lazy to type a `=CHAR(149)`

to get a bullet point. But Suat doesn't realize that I can press the `Alt` key and `7` on the number keypad to get bullet points. So he better check all 192 potential bad characters because he never knows when I might surprise him.)

Getting rid of bad characters with `SUBSTITUTE`

would take a long time. To be really safe, you would have to use one `SUBSTITUTE`

function for each of the 192 characters. Since you can not nest more than 64 functions, you would have to break the formula into four columns. Even if you tried to only handle the 31 illegal characters that are on a U.S. keyboard, the problem would be overwhelming.

The process of converting the above phrase to "why-i-say-bazinga-my-1-reason-for-using-index-match-" is called turning the title into a slug. The people who deal with web pages have made word this into a verb, saying that you have to "slugify" next month's article titles.

If you were going to build this in regular Excel formulas, the logic might go like this:

1. Start with character position 1. Store this in a cell column that we will call Index.

2. Convert that character to lowercase using `LOWER()`

3. See what ASCII code that letter is.

4. If the code for that letter between 48-57 or between 97 to 122? If it is, then use that character. If it is not, then use a hyphen.

5. Take the characters to the left of index, the new character from step 4, and the characters to the right of index. This is the new phrase.

6. Add 1 to the Index.

7. Check to see if the new Index is greater than the number of characters in the title. If it is, then do a final `SUBSTITUTE`

to replace any instances of a double -- with a single hypen. You are done. Stop calculating.

8. But if the Index is not greater than the `LEN`

, go to step 2, this time processing the 2nd character.

It is a crazy worksheet. I managed to convert my article title using just 630 formulas in A3:J65. Here is a tiny fraction of the worksheet.

At the end of the worksheet, the index is finally greater than the `LEN`

, and you finally get an answer in cell J65.

I want us to agree that the above method is pretty ugly and not really reasonable. I don't want to have to explain the formulas that I used above, since we are abandoning that method. However, in case you really want to see the formulas, here they are:

All of the above is ugly. 630 formulas. If the phrase gets longer, you will need more formulas.

It can all be replaced with one `LAMBDA`

function stored in a name called `SLUGIFY`

.

As the author of this book, I am hoping you ignored everything above this. But I really hope you try and understand everything that follows. This is the part that shows you how recursive LAMBDAs work.

Start the function with `=LAMBDA`

and name the two arguments that will be passed to it. The first argument is a phrase. The second argument will always be a 1 and will be stored in a variable called "ndx" because this sounds like "index" but is 40% shorter.

Tip

For the VBA programmers reading this, "ndx" is our loop counter. Unlike VBA where you could just create a variable on the fly, for LAMBDA, we have to send a 1 in as an argument. Trust me - it is a small compromise.

Start off with an `IF`

function that checks to see if we are done. Is the ndx beyond the end of the phrase?.

The part in the box below is the Value_if_True part of the IF statement. If we aren't done with Slugify, then call Slugify again, but make the Index be 1 more than the last time. The inner logic is described after the figure.

These six lines are the part of the formula that build the PHRASE for the next call to Slugify. If you use Excel frequently, all of the functions in this part should feel familiar. Use `LOWER`

and `MID`

to isolate this character. Get the `CODE`

of that character. If the `CODE`

is a letter or digit, then use it, otherwise use a hyphen. The phrase for the next time is going to be the left part, the newly fixed character, and the right part.

Deep inside the `LAMBDA`

is the one line that really does the work. If we have a letter or a digit, keep it. If it is one of the other 192 bad characters, then use a hyphen.

The first time you call Slugify, it will be fixing the first character. The Excel calculation engine will then call Slugify to fix the 2nd character. This keeps going. Eventually, Excel calls Slugify to fix the 64th character, but that's bad because there are only 63 characters in this phrase. So, when ndx is greater than the `LEN`

of the phrase, it is time to finally return the result.

The `SUBSTITUTE`

/ `TRIM`

/ `SUBSTITUTE`

logic replaces any multiple hyphens with a single hyphen.

Note that up to this point, I've been building the `LAMBDA`

in notepad or in a textbox in Excel.

So, you've written your `LAMBDA`

. You have to give it a name so you can call it. On the Formulas tab, click New Name. Name it Slugify. Paste the formula from the textbox, spaces and all into the Refers To box.

Here is the payoff... you now have a function called `SLUGIFY`

that you can use repeatedly.

The rebel in me (and perhaps in you) wonders what would happen if I changed the 1 in the second argument to 5. What do I get with `=Slugify(A2,5)`

? I tried it. It sort of works. It just does not fix any of the first 4 characters.

Before `LAMBDA`

, I would have created this as a VBA user-defined function. That requires you to understand VBA. And it won't ever work in Excel Online. Now, with `LAMBDA`

, you can simply use familiar Excel logic to build a function that loops through all of the characters in a cell.

Bonus Tip: Branching in a LAMBDA

On an earlier version of the previous `LAMBDA`

, I was getting multiple hyphens in the result. I wondered if you could have a second loop to clean those. Could you do the first task - replacing illegal characters with dash, and then have a second task of looping to look for consecutive hyphens.

Later, I realized that replacing - with space, using `TRIM`

, and then replacing space with hyphen solved the problem without the need for a second loop. However, this formula assigned to the name Slugify.Plus is an example of branching, so I am leaving it in.

The following formula offers two routines. The Branch parameter controls which part of the code is used.

Note that the ndx argument is not used in Branch 2. But since `LAMBDA`

arguments are not optional, you have to put something - I chose 0 - when calling branch 2.

Thanks to Suat Ozgur and Richard Simpson for help with Recursion and Branching.

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

*Title photo by Artem Kniaz on Unsplash*