Course / Modern Excel

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.

The sheet is a small business's net cash flow for each month of the year.

Steps

  1. In C2, build a running balance that starts from 0: =SCAN(0, B2:B13, LAMBDA(balance, cash, balance+cash))

  2. The business did not start the year at zero. Edit C2 so the balance starts from the opening balance in F1 instead of 0.

  3. 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)))

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

In the function reference: IF, LAMBDA, MAX, REDUCE, SCAN

Previous: Your own calculations: LAMBDA, BYROW and MAP · Next: Moving and selecting at speed