Running results: SCAN and REDUCE
Build a running balance with SCAN, start it from a cell, count with REDUCE, and find the longest losing streak with one formula. Advanced · 6 min · SCAN, REDUCE, Running totals, Accumulators
SCAN and REDUCE walk down a list one value at a time, carrying a result from each step to the next. That carried value is called the accumulator.
SCAN(start, list, LAMBDA(acc, x, …))spills the accumulator after every step: a running total, a running maximum, a streak counterREDUCE(start, list, LAMBDA(acc, x, …))gives only the final accumulator
The sheet is a small business's net cash flow for each month of the year.
Steps
In
C2, build a running balance that starts from 0:=SCAN(0, B2:B13, LAMBDA(balance, cash, balance+cash))The business did not start the year at zero. Edit
C2so the balance starts from the opening balance inF1instead of 0.How many months lost money? REDUCE carries a count down the list and keeps only the final number. In
F3:=REDUCE(0, B2:B13, LAMBDA(count, cash, IF(cash<0, count+1, count)))What was the longest run of losing months in a row? SCAN can count a streak that resets to 0 after a good month, and MAX picks the longest. In
F4:=MAX(SCAN(0, B2:B13, LAMBDA(run, cash, IF(cash<0, run+1, 0))))
Remember
SCAN(start, list, LAMBDA(acc, x, …))spills the running result after every value.REDUCE(start, list, LAMBDA(acc, x, …))gives only the final result.- The first argument is where the accumulator starts: 0 for a total, a cell for an opening balance.
- A streak resets the accumulator:
IF(test, run+1, 0).
In the function reference: IF, LAMBDA, MAX, REDUCE, SCAN
Previous: Your own calculations: LAMBDA, BYROW and MAP · Next: Moving and selecting at speed