# Excel 2024: New LAMBDA Helper Functions

October 09, 2024 - by Bill Jelen

About a year after `LAMBDA`

was introduced, Microsoft realized that we needed some helper functions. They gave us `MAP`

, `REDUCE`

, `SCAN`

, `MAKEARRAY`

, `BYROW`

, `BYCOL`

, and `ISOMITTED`

.

- The
`MAP`

function runs a`LAMBDA`

on each cell in an array or range and returns an identical-sized array or range. - The
`REDUCE`

function runs a`LAMBDA`

on each cell in an array or range but uses an accumulator variable to return one single answer. - The
`SCAN`

function is sort of a combination of the two. It runs a`LAMBDA`

on each cell of an array or range and returns an array the same size as the input range, showing the accumulator value after each step. - The
`MAKEARRAY`

function will create an array of any size that you specify. You provide a`LAMBDA`

to calculate each cell in the new array. - The
`BYROW`

function forces a`LAMBDA`

to calculate on each row in a range instead of the entire range. - The
`BYCOL`

function forces a`LAMBDA`

to calculate on each column in a range. - LAMBDAs now support optional arguments. You can test if an optional argument was skipped using the new
`ISOMITTED`

function.

Evaluating a `LAMBDA`

for Each Cell in a Range or Ranges

The `MAP`

function will perform a `LAMBDA`

calculation for each cell in a range. In the example below, you are passing two ranges to `MAP`

. Because there are two incoming ranges, your `LAMBDA`

needs two incoming variables A and B. Notice that each of the incoming arrays are 5 rows by 3 columns and the result from `MAP`

is also 5 rows by 3 columns.

Note that `MAP`

can accept multiple incoming arrays. This is not true for `REDUCE`

, discussed next.

Accumulating a `LAMBDA`

for Each Cell Using `REDUCE`

With `REDUCE`

, a `LAMBDA`

will be evaluated for each cell in an incoming array or range. On each pass through the logic, the result of the `LAMBDA`

can be added to an accumulator variable. At the end of the calculation, the formula returns the final value of the accumulator variable.

In this image, a `REDUCE`

formula in B8 calculates the total bonus pool after several shifts. The initial value is set to 0. The incoming array is each cell in B2:D6. Inside the `LAMBDA`

, the first two arguments are the variable for the accumulator and for the cell from the incoming array. The last argument in the `LAMBDA`

is the logic. Notice how the logic is adding the previous value of the accumulator to some calculation from this cell of the incoming range.

Seeing the Results From Each Step of `REDUCE`

with `SCAN`

The `SCAN`

function performs the same calculation as `REDUCE`

shown on the previous page. However, instead of returning a single value, it shows each intermediate value along the way.

In the image below, the Monday morning shift with sales of $1533 did not qualify for a bonus, so B8 shows 0. The Monday afternoon shift qualified for a $100 bonus, so C8 shows the total bonus earned so far is $100. The Monday evening shift earned another $100 for the bonus pool, so the total bonus as of the end of Monday is $200 shown in D8. Notice how the $3100 in sales for Tuesday evening kicked the bonus pool up from $200 to $800, with the $800 being shown in D9.

Evaluate a `LAMBDA`

for Each Row or Column

Say that you asked for the `MAX(A5:D11)`

. You would get one single number that was the largest value in the range. Sometimes, though, it would be good to have `MAX`

run on a column-by-column basis or a row-by-row basis and return the results as a spillable array. The `BYCOL`

and `BYROW`

functions allow you to do this.

Note that the `MAX`

in the above formulas is an Eta-Lambda introduced in November 2023. Before the Eta-Lambdas were introduced, you would use `LAMBDA(A,Max(A))`

.

Make an Array of Any Size

The `MAKEARRAY`

function lets you specify a number of rows and columns for the new array. The third argument is a `LAMBDA`

function with three arguments. The first is the row number. The second is the column number. The third argument is the logic to apply to this cell of the array.

Thanks to Chris Gross and his team in Redmond for these great new `LAMBDA`

helper functions.

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

*Title photo by Matthew Waring on Unsplash*