SCAN function

The SCAN function in Excel is a dynamic array function introduced in Excel 365 that processes an array or range of data by applying a function across the elements in the array. It returns an array of intermediate results, which represents the cumulative application of the function. Essentially, it’s a way to scan through an array step-by-step and generate intermediate results based on a specified operation.

Syntax:

=SCAN(initial_value, array, LAMBDA(accumulator, current_value, formula))

• initial_value: The starting value for the scanning process. This value is used in the first iteration and is updated as the function moves through the array.
• array: The range or array to be processed.
• LAMBDA(accumulator, current_value, formula): A custom function, defined using LAMBDA, which takes two parameters:
  • accumulator: The result of the previous iteration (or the initial value in the first iteration).
  • current_value: The current element of the array being processed.
  • formula: The operation to apply to both the accumulator and the current value.

Example 1: Cumulative Sum of an Array

Suppose you have an array {1, 2, 3, 4} in cells A1:A4 and you want to calculate the cumulative sum. You can use the SCAN function like this:

=SCAN(0, A1:A4, LAMBDA(acc, x, acc + x))

• initial_value is 0 (starting sum).
• The array is A1:A4 (which contains {1, 2, 3, 4}).
• The LAMBDA function adds the current element x to the accumulator acc.

This will return the array {1, 3, 6, 10}, which shows the cumulative sum of the array as you scan through the elements.

Example 2: Cumulative Product of an Array

If you want to calculate the cumulative product of the array {2, 3, 4}, you can use:

=SCAN(1, A1:A3, LAMBDA(acc, x, acc * x))

• initial_value is 1 (since multiplying by 1 doesn’t affect the result).
• The array is A1:A3 (which contains {2, 3, 4}).
• The LAMBDA function multiplies the current element x by the accumulator acc.

This will return the array {2, 6, 24}, representing the cumulative product.

Example 3: Cumulative Concatenation of Text

To concatenate text values in an array, suppose A1:A3 contains {"Hello", " ", "World"}. Use:

=SCAN("", A1:A3, LAMBDA(acc, x, acc & x))

• initial_value is "" (an empty string).
• The array is A1:A3 (containing {"Hello", " ", "World"}).
• The LAMBDA function concatenates the current string x to the accumulator acc.

This will return the array {"Hello", "Hello ", "Hello World"}, showing how the text is progressively concatenated.

Use Cases:

• Cumulative calculations: You can use SCAN for operations like running totals, cumulative sums, products, or averages.
• Cumulative text manipulations: Concatenate text values step-by-step as you process each element.
• Custom aggregations: More advanced operations such as custom logic or transformations on the array’s elements can be done incrementally.

Key Differences Between SCAN and REDUCE:

• SCAN returns an array of intermediate results (it “scans” the array step-by-step and provides results at each stage).
• REDUCE returns a single final value by applying the function across all elements of the array.

Benefits:

• Dynamic Arrays: Just like other dynamic array functions, SCAN spills the result into adjacent cells automatically.
• Customizable Operations: The use of LAMBDA allows you to define your own operations or transformations.
• Cumulative Results: It’s ideal for calculating progressive results, such as running totals or sequential transformations.

The SCAN function is especially useful when you need to track intermediate values as you process an array, rather than just the final outcome.