Excel 2024: New LAMBDA Helper Functions

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.