Level 3LET and LAMBDAReading, 5 min
SCAN, REDUCE, BYCOL and MAKEARRAY
MAP and BYROW run your LAMBDA once per row. Four more helpers cover the other shapes of problem:
| Helper | Hands your LAMBDA | Returns |
|---|---|---|
SCAN | the running value and the next item | every step: a running total |
REDUCE | the running value and the next item | only the final value |
BYCOL | one whole column | one result per column |
MAKEARRAY | a row number and a column number | a grid you size |
SCAN and REDUCE walk a list carrying a value along. =SCAN(E2, B2:B9, LAMBDA(balance, net, balance + net)) starts at the opening cash in E2 and shows the balance after each week. Swap SCAN for REDUCE and you get just the closing balance. The pair shines when the step isn't plain addition. Compounding a year of uneven monthly growth rates is =REDUCE(D2, B2:B13, LAMBDA(mrr, g, mrr*(1+g))): each month grows from the month before, which no SUM can do.
BYCOL is BYROW turned on its side. =BYCOL(C2:F17, LAMBDA(column, SUM(column))) totals four metric columns in one formula, which is the totals row of most dashboards. Swap SUM for MAX, AVERAGE or COUNTIF to get a different summary row.
MAKEARRAY(rows, columns, LAMBDA(r, c, ...)) builds a grid cell by cell from its position. A multiplication table is =MAKEARRAY(10, 10, LAMBDA(r, c, r*c)). In models it builds sensitivity tables: INDEX(prices, r) for the row's input and INDEX(volumes, c) for the column's.
The LAMBDA's inputs always arrive in the same order: for SCAN and REDUCE the running value first, then the item. Name them so the formula reads like a sentence: balance, net beats a, b.