As if Lambda() was already a great addition to Excel 365, Microsoft is adding what they call ‘helper’ functions to add even more abilities.
Here’s a very short overview of each helper function with links to more information. In some cases, the helper functions are a new and better way to do things already possible in Excel. Some others add new abilities not possible without VBA intervention.
We prefer to call them Lambda ‘extensions’:
- BYROW: a selection of results by applying LAMBDA to each row.
- BYCOL: a selection of results by applying LAMBDA to each column.
- MAP: makes a clone array created by mapping each value in the array(s) and applying the LAMBDA function to create a new value.
- REDUCE: applies a LAMBDA function to each value in a table/array and returns single summary value.
- SCAN: Scans an array by using a LAMBDA for each value, returning an array with each mid-value.
- MAKEARRAY: creates an array of a given row and column size, using a LAMBDA function to fill each cell.
We’ve already explained IsOmitted() another new function that works with Lambda’s new ability to accept optional parameters.
In all these example the variable names in the Lambda() is just a label to identify the cell value then pass it into the calculation. Use any valid name you like. Microsoft uses ‘value’, ‘row’ or ‘col’ but ‘squirrel’, ‘Adelaide’, ‘captain’ or ‘x’ would work just as well.
Our examples are deliberately very simple because the Lambda() helper functions can be a little daunting at first. The input and output is different for each helper. There’s a lot more possible beyond these ‘helper’ examples that get you started.
BYCOL() and ByRow()
A quick and easy way to calculate each column or row of a table or array is with the ByCol() or ByRow() functions. For many people these two functions might be the most immediately useful of the Lambda() helper functions.
Let’s say you have a table of data for your monthly expenses, and you’re concerned in finding out how much you spend monthly.
Until now, you’d have a SUM() or similar formula in each cell of the bottom ‘Total’ row. That works but there’s always the risk of a single cell being accidentally changed. ByCol lets you put a single formula that fills the whole row with a ‘per column’ calculation.
The formula for BYCOL in the bottom left cell is:
=BYCOL(Expenses[#All], LAMBDA(col, SUM(col)))
The syntax for ByCol is:
=BYCOL (array, lambda(column))
For ByRow it’s:
=BYRow (array, lambda(row))
With ByCol(), only one formula is needed in the first (left) cell. That will populate all the cells to the right, as far as the array/table goes.
In other words, the Lambda() function accepts the Column (col) value and calculates the total for that column. The BYCOL function uses LAMBDA and returns a result for each column. ByRow returns a result for each row.
Having a single formula ensures the calculations for each column/row are consistent and applied if the array is extended.
The above example could also be used to get totals for each category (row) using ByRow() to add up the monthly values and show a total of expenses in a new column at right.
Input: a table or array
Lambda: needs a column or row reference as the first parameter.
Returns: a value for each row or column in the source table/array.
More info on the Microsoft page BYROW and BYCOL and there’s a good example in Microsoft’s Excel Blog
Map
Map() makes a clone or copy of a source table/array changing each cell value according to the Lambda() function.
That’s possible now with a series of formulas referencing the original table in a matching table. Map() means the whole thing can be done with a single formula which changes as the source table does.
The examples Microsoft gives are good but a bit confusing, so we’ve made our own, even simpler example. Starting with a 3 x 3 table called Digits containing, drumroll…. digits!
Then there are three examples of MAP() at work based on the Digits table. Only three MAP() functions were necessary to make the three cloned tables. Those functions are in the top left cell (A7, A12 and A17) and displayed in the heading above.
COPIED – the most basic example, merely copies the values from Digits with no change (the INT() function does nothing).
DOUBLED – a clone of the Digits table but each value is doubled (number*2).
SQUARE ROOT – another clone of Digits with the square root of the original number (Sqrt(Number).
Map could be used to clone past results with changes to make future projections/targets (adding a percentage increase).
Input: a table or array
Lambda: takes a cell value as the first parameter.
Returns: a table or array of the same dimensions as the source.
More info on the Microsoft page MAP
Reduce
Reduce() takes a table or array and does a calculation on all the values to come up with a single result.
Each cell value is passed to the Lambda function in turn. The temporary result (between each Lambda() calculation) is held in an accumulator variable.
Microsoft’s Map() example is good, we’ll just reduce it a little more … pun intended.
The result formula counts the number of values that are greater than 50 but less than 80 (i.e. 55, 55 and 60):
=REDUCE(Table1[Values], LAMBDA(accumulator, value, IF(AND(value>50, value<80), 1 + accumulator, accumulator)))
Each time the IF statement is true, the accumulator adds 1. When false, the accumulator doesn’t change and is passed into the next cycle (cell).
Of course a CountIF() could do the same job but a lot more complex calculations can be inside the Lambda().
Input: a table or array
Lambda: needs an accumulator to hold the final result as the first parameter. The current cell value is the second parameter.
Returns: a single value
More info on the Microsoft page REDUCE
Scan
Reduce() calculates a single value after processing all the cells in the table/array. The interim steps of the calculation aren’t shown.
Scan() returns an array showing the result of each step as the Lambda() calculation works on each source cell. In other words, it shows a running total or calculation.
Here’s another v. simple example of a running total made with a single formula.
=SCAN(0,Results[Result],LAMBDA(accum,number,accum+number))
Input: a table or array
Lambda: needs an accumulator to hold the final result as the first parameter. The current cell value is the second parameter.
Returns: an accumulator value is returned for each cell/value processed.
MakeArray
MakeArray does as it says on the label, it makes a new array based on the values from a Lambda() function.
The Lambda() function is called for each cell in the new array. There’s no accumulator so each calculation is separate.
Here’s a variation on Microsoft’s example to make a randomize list of (Goonish) names.
=MAKEARRAY(10,1,LAMBDA(row,col,CHOOSE(RANDBETWEEN(1,3),"Neddie","Bluebottle","Eccles")))
Notice that the Lambda() needs row and column references as the first two parameters BUT those values don’t have to be used in the calculation.
Input: the dimensions of the array in rows and columns.
Lambda: row and column references as the first two parameters.
Returns: the array with Lambda() calculations in each cell.
More info on the Microsoft page MAKEARRAY
Who get the Lambda() helper functions?
For the moment, these extra Lambda() features are available to Office Insiders. Current Channel Preview for Windows v2107 build 14228.20154, Mac version 16.51 build 21071100.
New Lambda options and helper functions for Excel
Lambda – strange name for a really good extra in Excel
Lambda() downsides need to be fixed
