Calculating running balances, progressive tax tiers, or cumulative stock inventory in Microsoft Excel has traditionally required dragging formulas down a column (such as =SUM($C$2:C2)). In large spreadsheets containing tens of thousands of rows, these legacy expanding range references create quadratic computational overhead ($O(n^2)$), causing workbook recalculations to lag significantly.
Microsoft introduced the SCAN LAMBDA helper function to perform cumulative operations across dynamic arrays. Unlike REDUCE, which collapses an array into a single final scalar result, SCAN outputs an intermediate array containing the value after each individual calculation step, allowing you to generate dynamic, instantly recalculating running totals from a single formula cell.
Syntax and Arguments of the SCAN Function
The SCAN function iterates through an array, maintaining an internal accumulator that updates at each element:
=SCAN([initial_value], array, LAMBDA(accumulator, value, calculation))
- initial_value (optional): The starting seed value for the calculation (typically
0for running sums or1for running products). - array: The range or dynamic array of values to be evaluated (for example,
C2:C20). - LAMBDA(accumulator, value, calculation): A custom function with three parts:
accumulator(e.g.a): Stores the running total returned by previous iterations.value(e.g.v): Represents the current element being processed in the array.calculation: The mathematical or string expression combining the accumulator and current value.
Calculating a Dynamic Running Total with SCAN
Consider a transaction register where column C2:C20 contains daily sales figures:
- Select cell
D2where your running total column should begin. - Enter the following
SCANformula:
=SCAN(0, C2:C20, LAMBDA(a, v, a + v))
Excel starts with 0, adds the first sales value in C2, returns the sum, carries it forward to add to C3, and spills a complete vertical vector of running totals from D2 to D20 automatically.
Calculating Running Balances with Inflows and Outflows
In bank reconciliation statements where revenue and expenses appear in separate columns:
- Suppose deposits reside in
B2:B20and withdrawals inC2:C20. - You can compute net changes and generate a running bank balance starting from an initial deposit of £1,000:
=SCAN(1000, B2:B20 - C2:C20, LAMBDA(bal, net, bal + net))
The formula dynamically computes net cash flow for each row and maintains an ongoing ledger balance.
Generating Cumulative String Joins and Audit Trails
SCAN is not restricted to numeric calculations; it can concatenate text vectors to build step-by-step audit paths:
- Suppose cells
A2:A6contain workflow status steps (e.g., Submitted, Reviewed, Approved, Dispatched). - To generate an expanding breadcrumb path at each stage:
=SCAN("", A2:A6, LAMBDA(path, step, IF(path="", step, path & " > " & step)))
The output array spills sequentially:
Submitted
Submitted > Reviewed
Submitted > Reviewed > Approved
Submitted > Reviewed > Approved > Dispatched
Performance and Memory Benefits
Using SCAN provides distinct advantages over legacy formulas:
- Linear Complexity: Operates in $O(n)$ linear time rather than $O(n^2)$, keeping multi-thousand row workbooks responsive.
- Dynamic Spill Support: When paired with dynamic ranges (such as
A2#), the output vector automatically expands or contracts whenever source datasets resize without requiring manual formula copying. - Error Containment: If an input cell contains an error (such as
#VALUE!), only that step and subsequent cumulative sums fail, leaving preceding historical steps intact for debugging.