Excel SCAN function

Summary

The Excel SCAN function progressively applies a LAMBDA to each element in an array and returns an array of all intermediate values. This function is particularly useful for generating running totals, cumulative statistics, or constructing sequences based on the array elements and a specified operation.
Syntax
				
					=SCAN([initial_value], array, lambda(accumulator, value))
				
			
  • [initial_value]: Optional. The starting value for the accumulator, influencing the initial state of the cumulative operation.
  • array: The array of values to be scanned.
  • lambda: A LAMBDA function that defines the operation on each array element. It takes the accumulator (the cumulative result so far) and the current value as parameters.
Return value
An array containing each intermediate result produced by the cumulative application of the LAMBDA function across the array.

How to use

Specify the array to scan and optionally provide an initial value for the accumulator, setting the stage for the cumulative calculations. Define a LAMBDA function that outlines how each element of the array contributes to the running total or sequence. This setup allows for tracking the evolution of the accumulator after each array element is processed, showcasing the progression of the applied operation.

Examples

Simple SCAN
Generating a List of Factorials: Creating a sequence of factorial values.
				
					=SCAN(1, A1:C2, LAMBDA(a, b, a * b))

				
			
This formula multiplies each number in the array A1:C2 by its predecessor’s product, effectively calculating factorial values, demonstrating SCAN’s ability to perform sequential multiplications.
SCAN for Running Totals
Creating a Running Total: Accumulating a sum across an array.
				
					=SCAN(0, {1, 2, 3}, LAMBDA(a, v, a + v))

				
			
Initiates with an accumulator of 0 and adds each element to the total, yielding a running sum of {1, 3, 6}, showcasing how SCAN can be used to create running totals.
SCAN with Text Concatenation
Concatenating Characters in an Array: Sequentially combining text values.
				
					=SCAN("", {"a", "b", "c"}, LAMBDA(a, v, a & v))

				
			
Starts with an empty string and concatenates each character, resulting in {“a”, “ab”, “abc”}, illustrating SCAN’s capability with text strings.
SCAN with Custom "PRODUCTIF"
Multiplying Selected Values: Creating a custom function to multiply values based on a condition.
				
					=SCAN(1, Table2[Nums], LAMBDA(a, b, IF(b > 50, a * b, a)))

				
			
Begins with 1 and conditionally multiplies it by each number greater than 50, applying SCAN to selectively aggregate values.
SCAN for Data Transformation
Transforming Data with Conditions: Applying a transformation based on a logical test.
				
					=SCAN(0, B5:B16, LAMBDA(a, n, IF(ISEVEN(n), a + 1, a)))

				
			
Uses an initial value of 0 to count even numbers in B5:B16, incrementing the accumulator for each even value, thus using SCAN to apply conditional logic across data.

Additional Notes

  • SCAN is adept at handling a wide range of data manipulation tasks, from simple aggregations to complex sequences and transformations.
  • By providing a dynamic way to visualize the step-by-step application of operations on array data, SCAN enhances data analysis and exploration within Excel.

Related Functions

Excel LAMBDA function

The Excel LAMBDA function creates custom functions without VBA, enabling complex calculations and reusable formula components.

Excel REDUCE function

The Excel REDUCE function aggregates elements of an array, allowing for cumulative calculations and data reduction operations.

Excel BYROW function

The Excel BYROW function applies a lambda function to each row in a range or array, facilitating row-based operations and analysis.

Excel BYCOL function

The Excel BYCOL function applies a lambda function to each column in a range or array, streamlining column-based calculations.

Excel LET function

The Excel LET function assigns names to calculation results, simplifying formulas and improving performance by reducing repetition.

Excel MAKEARRAY function

The Excel MAKEARRAY function generates arrays using lambda functions, offering flexibility in array creation and manipulation.

Content Navigation