Running totals are a fundamental component of data analysis, frequently used to track cumulative metrics such as sales, expenses, or inventory. However, calculating running totals in Excel has often been cumbersome, requiring manual effort or complex formulas. Traditional methods involved using helper columns, dragging formulas across rows, or writing nested functions like SUM combined with OFFSET or INDEX. While effective, these approaches are especially vulnerable to user errors. They’re also especially time-consuming, particularly when dealing with large datasets.
Excel has finally addressed this pain point with the introduction of a new function called SCAN. It’s designed to streamline calculations like running totals. By automating iterative computations across a range of data, SCAN eliminates the need for workarounds and brings unparalleled efficiency to Excel users.
What Is the SCAN Function?
The SCAN function, part of the new Lambda helper functions, performs iterative calculations across an array, returning an array of intermediate results. It works by applying a custom formula or logic cumulatively to each element in a range. For calculating running totals, SCAN evaluates each value in a sequence and adds it to the prior values, generating an ongoing cumulative sum.
Here’s the basic syntax for SCAN:
=SCAN([initial_value], array, lambda(accumulator, value))
- Initial_value: An optional starting point for the calculation.
- Array: The range of data to iterate through.
- Lambda: The formula applied cumulatively, where “accumulator” holds the running result, and “value” is the current element.
Here’s an example. To calculate a running total of a sales column, the formula might look like this:
=SCAN(0, Sales, LAMBDA(acc, val, acc + val))
This simple setup generates a running total without requiring auxiliary columns or advanced formula nesting.
How the SCAN Function Saves Time
Consider a business analyst tasked with maintaining monthly running totals for a company’s sales data, spanning thousands of rows. Previously, this would require a SUM formula manually adjusted for each row, such as =SUM($A$1:A1). Copying this formula across a large dataset often led to significant lag, especially with volatile functions like OFFSET.
With SCAN, the same task becomes a one-step operation. Instead of applying formulas to each row individually, the SCAN function processes the entire dataset at once, providing immediate results. For large datasets, this can save hours of work, reducing the risk of formula errors and improving overall efficiency.
Other Benefits of the SCAN Function
Beyond running totals, the SCAN function offers versatile applications, including:
- Cumulative Percentage Calculations
- In scenarios such as calculating cumulative contribution percentages (Pareto analysis), SCAN can apply the logic to dynamically compute cumulative shares of a total, streamlining the process.
- Custom Aggregations
- SCAN can execute more complex accumulations, such as multiplying values sequentially or applying conditional logic. For instance, tracking a compounded growth rate or applying a discount schedule becomes effortless with SCAN.
- Dynamic Simulations
- SCAN is invaluable for modeling scenarios like cash flow projections, where future values depend on cumulative totals. Using SCAN, users can integrate custom functions to model cascading effects or dynamic thresholds.
The SCAN function represents a paradigm shift in how Excel handles iterative calculations. By simplifying complex tasks and enhancing calculation speed, it empowers users to focus on insights rather than wrestling with formulas. Whether you’re a data analyst, financial planner, or operations manager, the SCAN function promises to make your workflow smoother, faster, and more reliable.
Visit Spreadsheet Point for in-depth guides to Excel and Google Sheets formulas.
