Google Sheets SCAN Function: Syntax, Examples & Tips

initial_value:
The starting value for the accumulation process, which can be a number, text, or any other data type that matches the expected output of the LAMBDA function.

array_or_range:
The array or range of cells that you want to process with the LAMBDA function. This is the data that will be evaluated and transformed based on the logic defined in the LAMBDA.

LAMBDA:
A custom function that defines how to process each element of the array_or_range. It takes parameters (usually the current accumulated value and the current element) and returns a new accumulated value based on the specified logic.

What is SCAN?

The SCAN function in Google Sheets is a powerful tool that allows users to apply a function cumulatively across a range or array, returning an array of intermediate results. This function is particularly useful for scenarios where you want to track the progression of calculations or transformations as you iterate through a dataset.

In simpler terms, SCAN enables you to see how a calculation evolves step by step as it processes each element in a range.

A Practical Example

Imagine you are tracking the daily sales of a product over a week and want to calculate the cumulative sales for each day:

Daily Sales Table:

You want to calculate the cumulative sales for each day, showing how sales accumulate over the week.

SCAN Formula

To achieve this, you would use the SCAN function as follows:

=SCAN(0, B2:B6, LAMBDA(acc, curr, acc + curr))

In this formula:

• 0 is the initial value (the starting point for accumulation).
• B2:B6 is the range containing the daily sales data.
• LAMBDA(acc, curr, acc + curr) is a custom function that takes the accumulated value (acc) and the current value (curr), adding them together to produce the new accumulated value.

Result of the Formula

When you apply the SCAN formula to the Daily Sales Table, the output would look like this:

As you can see, the Cumulative Sales column shows how the total sales accumulate day by day.

Why Use SCAN?

SCAN is beneficial because it allows for a clear visualization of how values accumulate over time or through a dataset. It simplifies the process of tracking cumulative totals without the need for complex formulas or additional columns. This makes your spreadsheets more efficient and easier to understand.

Key Takeaways:

• SCAN: A modern Google Sheets function that applies a cumulative function across a range, returning intermediate results.
• Cumulative Calculations: Ideal for tracking totals, averages, or any scenario where understanding progression is essential.
• Custom Functions: Allows users to define their own logic for accumulation using LAMBDA functions, enhancing flexibility.
• Common Use Cases: Useful for financial tracking, performance metrics, and any situation where cumulative data is relevant.

SCAN is an essential function for anyone working with data in Google Sheets, providing a powerful way to enhance data analysis and reporting capabilities.

Happy spreadsheeting!