Excel like a finance pro.
← The libraryPractice · 3 questions →
Logical function
SCAN
Like REDUCE but returns every intermediate value, useful for running totals.
OccasionalDifficulty 1800 · ExpertUsage rank #102 of 520
When to use it
Like REDUCE, but returns every intermediate accumulator value. Running totals, running maximums, and compounding balances.
The shape of it
- Syntax
=SCAN([initial_value], array, lambda)
Worked examples
Running total
=SCAN(0,{1,2,3,4},LAMBDA(a,x,a+x)) → {1,3,6,10}
A running total.
Compounding balance
=SCAN(100,{0.1,0.2,-0.05},LAMBDA(v,r,v*(1+r))) → {110,132,125.4}
A balance compounding through three returns.
Running max
=SCAN(0,{3,1,4,1,5},LAMBDA(a,x,MAX(a,x))) → {3,3,4,4,5}
A running maximum.
Worth knowing
- Replaces the classic =B2+C1 copy-down running total with one spilling formula.
- Initial value is the starting balance.
- Works on rows or columns.
Where it goes wrong
- Requires Microsoft 365.
- Large arrays can be slow.
Related
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.