Excel like a finance pro.
MAP, REDUCE & SCAN
Dynamic arrays (Microsoft 365)SCAN is a running balance in one formula — which is the cash-forecast shape exactly.
Difficulty
1What is it?
Three functions that complete the LAMBDA family. MAP walks every cell and returns a result per cell. REDUCE walks them accumulating one running answer and returns only the final value. SCAN is REDUCE that shows its working: it returns every intermediate step, which is precisely a running balance. A thirteen-week cash forecast is an opening balance plus a strip of movements, accumulated — normally a column of `=C4+D5` dragged down, and with SCAN it is one formula that can't be dragged wrong or broken by an inserted row.
2What it looks like
MAP(array, LAMBDA(v, calc)) · REDUCE(initial, array, LAMBDA(acc, v, calc)) · SCAN(initial, array, LAMBDA(acc, v, calc))- array
- The values to walk.
- initial
- The starting accumulator — the opening cash balance, for a forecast.
- LAMBDA(acc, v, …)
- TWO parameters for REDUCE and SCAN: the accumulator so far and the current value. MAP's LAMBDA takes only the value.
3When you use it
- A running cash balance across thirteen weeks, as one formula.
- Compound a growth rate across periods where each step depends on the last.
- Apply a rule to every cell of a grid without a parallel grid of formulas.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
An opening balance and four weekly movements. SCAN shows every step; REDUCE shows only the last.
=SCAN(A2, B2:E2, LAMBDA(acc, v, acc + v))REDUCE with the same LAMBDA returns only $76,000 — the last step.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Opening | Wk 1 | Wk 2 | Wk 3 | Wk 4 | Result |
| 2 | REDUCE with the same LAMBDA returns only $76,000 — the last step. | |||||
| 3 | SCAN returns all four, which is the forecast line. | |||||
| 4 | Lowest point $76,000. |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#VALUE!The LAMBDA has the wrong number of parameters — one for MAP, two for REDUCE and SCAN.Fix: SCAN and REDUCE always supply both the accumulator and the value, in that order, whether you use them or not.
#CALC!The LAMBDA returns an array instead of a single value.Fix: Each step must collapse to one value. Wrap in SUM or MAX if it doesn't.
REDUCE gave one number, you wanted the seriesThat's the difference between the two: REDUCE returns only the final accumulator.Fix: Use SCAN, which returns every step. They're otherwise identical.
Slow on a large rangeThese iterate in the formula engine rather than vectorising.Fix: Fine for a 13-week strip. For tens of thousands of rows, a helper column is genuinely faster.
6Better functions & alternatives
- A running-total column — =C4+D5 dragged down works everywhere and is easier to read. SCAN's advantage is that it can't be dragged wrong or broken by an inserted row.
- BYROW / BYCOL — For one value per row or column rather than an accumulation along a series.
Want MAP, REDUCE & SCAN already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.