LAMBDA functions

(Originally posted July 23, 2021 By ~ Chris Gross)

Hi Insiders! I’m Chris Gross, a program manager on the Excel team. Late last year, we launched a new Lambda functionThis allows you to create custom Excel functions. Today, we are excited to announce seven new LAMBDA functions and other improvements to this feature.

New LAMBDA function available in Excel

Over the past few years, we’ve been “teaching” Excel how to understand new types of values. Some of the most recent additions have been data types (geography, stocks, Power BI, and Power Query) and dynamic arrays.

LAMBDA continues this effort by allowing Excel to understand functions as values. (This was made possible by the introduction of LAMBDA, but this functionality was not initially exposed to any functions.) This is interesting because it means that calculations that were previously very difficult or impossible to perform can now be performed by writing a LAMBDA and passing it as a value to a function.

New LAMBDA features

The seven new LAMBDA functions being introduced are:

• map: Returns an array formed by “mapping” each value in the array to a new value and applying LAMBDA to generate the new value.
• reduce: Reduces an array to an accumulated value by applying the LAMBDA function to each value and returning the total value from the accumulator.
• injection: Scans the array by applying LAMBDA to each value and returns an array containing each median value.
• MakeArray: Returns a computed array of the specified row and column sizes by applying the LAMBDA function.
• Byrow: Applies LAMBDA to each row and returns an array of results.
• Bycol: Applies LAMBDA to each column and returns an array of results.
• Listed: Checks if a value is missing and returns TRUE or FALSE.

Read on for some examples that illustrate some of these new features. For more information and additional examples, see: My posts on the Excel Tech Community site.

How it works

BYCOL function

The BYCOL function makes it easy to take large sets of data and return calculations for each column in the set.

For example, let’s say you have a data table of monthly expenses and you want to calculate how much you spend each month.

Sample data for the BYCOL LAMBDA function

You could write individual functions for each column, but then you run into problems when new data comes in or you want to change the calculation later. This is where the power of LAMBDA comes in handy, because it has the ability to encapsulate a single calculation and apply it to a set of data.

For BYCOL the formula is:

=BYCOL(Expenses[#All], LAMBDA(col, SUM(col)))

This calculates the total cost for each month in the table.

Sample data for BYCOL LAMBDA function (including totals)

It is important to note that BYCOL passes a single parameter to the LAMBDA function, corresponding to the columns in the array. BYCOL calls LAMBDA and returns the results for each column. It is important to remember that BYCOL passes a single parameter because each function expects a different set of parameters and may (depending on the function) return one or more results.

Scan function

In the following example, let’s say we want to calculate the cumulative sales for each month.

Sample data for the SCAN LAMBDA function

This is a perfect use case for SCAN. This function takes two parameters, one of which is passed to each LAMBDA call.

The final solution is:

=SCAN(0, MonthlySales[Sales], LAMBDA(accumulator, value, accumulator+value))

SCAN “scans” an array by applying LAMBDA to each value. It then returns an array of results corresponding to the accumulator values ​​returned by LAMBDA. SCAN takes two parameters:

• Accumulator: The initial value returned by SCAN and each LAMBDA call.
• value: The values ​​in the provided array.

Another thing to note is the first optional argument, “[initial value]” About the accumulator. In our case, we want it to be 0.

The accumulator is what allows us to return a running total, all with just one formula!

Sample data for the SCAN LAMBDA function. Includes cumulative totals.

MAKEARRAY function

MAKEARRAY allows you to create arrays using LAMBDA calculations. This function is useful for situations where you want to combine or transform arrays, but it is also useful for generating data.

Let’s say we use MAKEARRAY with CHOOSE and RANDBETWEEN to generate a list of random colors. The final solution would be:

=MAKEARRAY(A2, B2, LAMBDA(row, col, CHOOSE(RANDBETWEEN(1,5),"Red","Blue","Green","Yellow","Orange")))

The first two arguments to MAKEARRAY are A2 and B2, which correspond to the number of rows and columns to be generated. The last argument to MAKEARRAY is LAMBDA, which takes two parameters corresponding to the values ​​generated by LAMBDA.

• heat: Index of the row.
• safety: The index of the column.

The LAMBDA calculation uses RANDBETWEEN with CHOOSE to generate a random number that corresponds to a possible number of choices (in this case, 5). For example, if Excel generates the number 3, the value “Green” is selected. If it generates 5, the value “Orange” is selected.

Once the formula is written, we can easily generate large amounts of data. The final output is as follows:

Sample data for the MAKEARRAY LAMBDA function

LAMBDA Improvement

We are also pleased to announce that we have added support for optional parameters within the LAMDBA function. To use an optional parameter, use the optional name “[ ]”. for example:

=LAMBDA(param1, [param2], IF(ISOMITTED(param2), param1, param2))

This LAMBDA returns the value of param1 if param2 is omitted, otherwise it returns the value of param2.

effectiveness

These new features are now available to Office Insiders running Beta Channel Version 2108 (Build 14312.20008) or later on Windows, or Beta Channel Version 16.52 (Build 21072100) or later on Mac.

The LAMBDA function itself has been released to Office Insiders running Current Channel Preview version 2107 (Build 14228.20154) or later on Windows, or version 16.51 (Build 21071101) or later on Mac.

Features are released over time to ensure everything works smoothly. They are released slowly to a larger number of Insiders, highlighting features you may not have. Sometimes we remove elements based on your feedback to make them even better. This is rare, but we also have the option to completely remove a feature from the product, even if you, as an Insider, had the opportunity to try it out.

feedback

We want to hear from you! Go to: help > feedback Please let us know what you think about this feature.

Learn about Microsoft 365 Insider Program And join us Microsoft 365 Insider Newsletter Get the latest Insider features straight to your inbox once a month!