Your own calculations: LAMBDA, BYROW and MAP
Give each row of a table its own result with BYROW, each column with BYCOL, and each value with MAP, using a small LAMBDA to say what to work out. Advanced · 7 min · LAMBDA, BYROW, BYCOL, MAP
Some calculations cannot spill on their own. =SUM(B2:E7) adds up the whole table into one number, but what if you want one total per row?
LAMBDA writes a small calculation with a name for its input: LAMBDA(row, SUM(row)) means "given a row, add it up". Three functions run a LAMBDA over a table:
BYROW(table, LAMBDA(row, …)): one result per rowBYCOL(table, LAMBDA(col, …)): one result per columnMAP(range, LAMBDA(x, …)): one result per value, and it can walk several ranges in step
The sheet is a coffee roaster's units sold per quarter.
Steps
In
H2, give each coffee its total for the year, one per row:=BYROW(B2:E7, LAMBDA(row, SUM(row)))Which coffees sold at least 100 in every quarter?
ANDturns a whole row of tests into one answer, which is exactly what BYROW needs. InI2:=BYROW(B2:E7, LAMBDA(row, AND(row>=100)))Now the other direction: a total for each quarter. In
B8:=BYCOL(B2:E7, LAMBDA(col, SUM(col)))Reorder when stock is below a third of last quarter's sales. MAP can walk two ranges in step, giving the LAMBDA one value from each. In
J2:=MAP(F2:F7, E2:E7, LAMBDA(stock, sold, IF(stock<sold/3, "Reorder", "OK")))
Remember
LAMBDA(name, calculation)is a small calculation with a name for its input.BYROW(table, LAMBDA(row, …))gives one result per row;BYCOLone per column.MAP(range1, range2, LAMBDA(a, b, …))gives one result per value, walking the ranges in step.- Use them when the calculation sums, tests with AND or OR, or otherwise turns a row into one value.
In the function reference: AND, BYCOL, BYROW, IF, LAMBDA, MAP, SUM
Previous: Combine lists: VSTACK and HSTACK · Next: Running results: SCAN and REDUCE