Excel: LAMBDA function

Excel: LAMBDA function

What is a LAMBDA function?

The LAMBDA function come from Alan Turing's (English mathematician, computer scientist, logician, cryptanalyst, philosopher, and theoretical biologist) professor Alonzo Church who invented LAMBDA calculus.

The LAMBDA function is named after Lambda calculus.

LAMBDA function moves the VBA User-Defined-Functions to the name manager (compatible with Excel Online without learning Typescript). You can define your own custom function using Excel's formula language. 

With LAMBDA you can take a formula that you have build 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.

It facilitates using recursion within a formula, which means you can use the function to call itself one or more times until a specified condition is met at which time the rest of each repetition is processed from the last one called to the first. 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).

With LAMBDA you have reuse and composability. Create libraries for any pieces of logic you plan to use multiple times. It offers convenience and reduces the risk of errors. For example if you have product item groups where the "categories" are encoded within the group and you want to pull that value out e.g. COUNT and GUM and CAN and SLA:

There are many ways to do this with Excel functions and one way to do it is:

=LEFT(RIGHT(T4,LEN(T4)-FIND("-",T4)),FIND("-",RIGHT(T4,LEN(T4)-FIND("-",T4)))-1)

If I take that formula and copy it down the column, I can get the results shown in the table above.

There are two challenges if I use this approach:

• Errors - If there were an error in the logic of this formula then I have to go back and fix this error everywhere it was used and I might miss some.
• Composability/Readability - If I am not the creator of the formula, it is hard to know what the intention of that formula is (n this example to pull out the category). It is also hard to use this logic in combination with other logic, like if I want to take the "category" and do a lookup based on the calculated "category".

Using LAMBDA I can create a function named GETCATEGORY, and put the formula logic into the definition of that function.

=GETCATEGORY

=LAMBDA(ITEM_GROUP, LEFT(RIGHT(ITEM_GROUP,LEN(ITEM_GROUP)-FIND("-",ITEM_GROUP)),FIND("-",RIGHT(ITEM_GROUP,LEN(ITEM_GROUP)-FIND("-",ITEM_GROUP)))-1))

Notice I specify the arguments my function will take (in this case ITEM_GROUP) and the logic for my function. In my spreadsheet I can simply write =GETCATEGORY as a formula and reference the cell that has the ITEM_GROUP, just like any other excel function. If I notice that I have an error I fix it in one place and everywhere that uses that function are fixed.

Another added benefit is that I can now compose that function with additional logic, for example if there are different rates of import taxes levied based on the category of product I can write this formula to return the tax rate of each ITEM GROUP:

=XLOOKUP(GETCATEGORY(T4), table1[locations], table1[tax]).

This capability can be used to build up a rich set of function libraries, make your sheets easier to understand and less error prone. These functions can even take data types as arguments.

One big missing pieces in Excel formulas has been the ability to loop or repeat a set of logic at a dynamically defined interval. 

Imagine I have a set of strings and I want to specify which characters should be removed from those strings dynamically:

The set of characters that you are specifying are not static. If it was you could do a ton of nested logic, but that would be pretty much complex and error prone and if the number of characters to be removed was larger than what you'd accounted for, it would fail.

With LAMBDA, we can create a function REPLACECHARS that references itself allowing you to iterate over the list of characters to be removed.

=REPLACECHARS

=LAMBDA(textString, illegalChars,

    IF(illegalChars = "", textString,

        REPLACECHARS(

        SUBSTITUTE(textString, LEFT(illegalChars, 1), ""),

        RIGHT(illegalChars, LEN(illegalChars)-1)

)))

Notice in the definition of REPLACECHARS, there is a reference to REPLACECHARS.  The if statement  says if there are no more illegal characters, return the input textString, and otherwise remove each leftmost character in illegalChars. Recusrsion kicks in with the request to call REPLACECHARS again with the updated string, and the rest of illegalChars. This mean it will keep calling itself until it has parsed over every character to be removed, giving the desired result.

There were significant improvements over the past couple of years, with the type of data you can work with in Excel.

• Dynamic Arrays: Rather than passing a single value into a function, you can pass an array of values, and functions can also return arrays of values. 
• Data Types:  The value stored in a cell is n longer just a string or number.  A single cell can contain a rich data type, with a large set of properties.

Functions can take data types and arrays as arguments, and they can also return results as data types and arrays. The same is true with the Lambdas that you build.

For example: I have a list of cities, and I want to calculate the total distance I'd travel if I were go to each city in order.

It's pretty basic.

• We have an array of City data types. The City data types have longitude and latitude properties. 
• With longitude and latitude, we can do some basic math using the radius of the earth to approximate the distance between two points (that is the first Lambda we call DistanceBetweenCities).
• We create a recursive lambda, DistanceBetweenMulitipleCities, to iterate over the cities in the array. In addition to calling itself, to iterate over the list of cities, it also calls the DistanceBetweenMulitipleCities function to get a running total of the distance travelled.

There are three key pieces of =LAMBDA to understand:

1. LAMBDA function components
2. Naming a lambda
3. Calling a lambda function

LAMBDA function components

Lets look at an example which create a basic lambda function.

Suppose we have the following formula:

=LAMBDA(x, x+122)

In this x is the argument that you can pass in when calling the LAMBDA, and x + 122 is the logic.

For example, suppose you called the lambda and  input the value 1 for x, Excel would do the following calculation:

1+ 122

Which as we all know:

1 + 122 = 123 

But how do we use this? If you have been pasting some of our examples into Excel, you may have noticed #CALC errors. To resolve those, you'll need to learn the next step.

Naming a LAMBDA

To give your LAMBDA a name so it can be re-used, you will want to make use of the Name Manager. The Name Manager can be found in the Ribbon by going to:

Formulas>Name Manager

Once you open the Name Manager you will see the following window:

 

From this point you will want to create a new entry (New...) and fill out the associated fields.

Name: The Name of your function

Comment: A description and associated tooltip which will be shown when calling your function

Refers to: Your lambda function definition

Once you’re done, you can hit “OK” to store your lambda and you should see the definition returned in the resultant window.

You can now make use of your newly crafted LAMBDA by calling it by its name.

Calling LAMBDA

You call a lambda function the same way you call native functions in Excel.

To illustrate this, lets revisit the previous example and show how to call MYLAMBDA with a value.

=MYLAMBDA(122)

Which of course returns the value:

123

One last thing to note, is that you can call a lambda without naming it. If we hadn’t named the previous formula, and just authored it in the grid, we could call it like this:

=LAMBDA(x, x+122)(1)

This passes in 1 for x, which returns

123