Calculating cumulative running totals, exponential moving balances, and text concatenations across dynamic datasets has traditionally presented hurdles in Microsoft Excel. Traditional solutions rely on dragging formulas with mixed cell references (e.g., =SUM($B$2:B2)) down hundreds of rows, which breaks dynamic array spilling and causes calculation lag when new rows are inserted. In modern Excel 365 and Excel 2024, Microsoft introduced two premier lambda helper functions: SCAN and REDUCE. These functions iterate through dynamic ranges to accumulate running values cleanly with zero formula copying.
The Difference Between SCAN and REDUCE
While both functions iterate through an array and apply accumulator logic, they differ fundamentally in their output dimensions:
- SCAN: Returns an array of intermediate accumulator results. For an input range of 50 rows, SCAN outputs a spilled array of 50 running values (e.g., a cumulative running total).
- REDUCE: Returns a single final collapsed scalar value. For an input range of 50 rows, REDUCE rolls all items into 1 single result (e.g., total accumulated value or a single concatenated string).
1. Calculating Running Totals with the SCAN Function
The standard syntax of the SCAN function is structured as follows:
=SCAN(initial_value, array, LAMBDA(accumulator, current_value, calculation))Step-by-Step Practical Example:
Suppose you have monthly revenue numbers in cells B2:B13 and want to generate a cumulative year-to-date running total starting from an initial baseline of zero (0):
=SCAN(0, B2:B13, LAMBDA(acc, val, acc + val))How the Formula Evaluates:
- Row 1: Accumulator starts at
0, addsval(Month 1), outputs Month 1 total. - Row 2: Accumulator holds Month 1, adds Month 2, outputs combined total.
- Row 3 through 12: Continues dynamically spilling intermediate totals down column C with zero formula copying.
2. Calculating Running Bank Account Balances with Transaction Fees
Because the lambda expression within SCAN supports full mathematical logic, you can calculate complex financial balances with conditional rules:
=SCAN(1000, B2:B50, LAMBDA(bal, tx, IF(tx<0, bal + tx - 2.50, bal + tx)))This formula starts with an opening bank balance of $1,000, applies debits and credits, and automatically deducts a $2.50 processing fee on any negative transaction (debit).
3. Collapsing Datasets into a Single Output with REDUCE
The REDUCE function uses identical parameters to SCAN but outputs only the final accumulated value:
=REDUCE(initial_value, array, LAMBDA(accumulator, current_value, calculation))Practical Example: Custom Delimited String Concatenation
To roll a column of product SKUs (A2:A20) into a single comma-separated string wrapped in brackets:
=REDUCE("", A2:A20, LAMBDA(acc, sku, IF(acc="", sku, acc & ", " & sku)))The calculation iterates through all 19 cells and outputs a single clean, formatted text string in one cell.
Comparison: Traditional SUM vs. SCAN Function
| Metric | Traditional =SUM($B$2:B2) | Modern =SCAN(0, B2:B100, …) |
|---|---|---|
| Dynamic Spilling | No (Must drag down each cell) | Yes (Single formula spills full column) |
| Calculation Overhead | O(N^2) quadratic expansion | O(N) linear single-pass calculation |
| Row Insertion Resilience | Breaks mixed absolute references | Fully resilient (Range bounds dynamically) |