Course / Modern Excel

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:

The sheet is a coffee roaster's units sold per quarter.

Steps

  1. In H2, give each coffee its total for the year, one per row: =BYROW(B2:E7, LAMBDA(row, SUM(row)))

  2. Which coffees sold at least 100 in every quarter? AND turns a whole row of tests into one answer, which is exactly what BYROW needs. In I2: =BYROW(B2:E7, LAMBDA(row, AND(row>=100)))

  3. Now the other direction: a total for each quarter. In B8: =BYCOL(B2:E7, LAMBDA(col, SUM(col)))

  4. 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

In the function reference: AND, BYCOL, BYROW, IF, LAMBDA, MAP, SUM

Previous: Combine lists: VSTACK and HSTACK · Next: Running results: SCAN and REDUCE