SCAN Function

The SCAN function applies a custom calculation to each element in a given array and returns an array that contains each intermediate value created during the scan. SCAN can generate running totals, running counts, and other calculations that create intermediate or incremental results. The results returned by SCAN are the value of an "accumulator" at each step in the process. 

Like the REDUCE function, SCAN iterates over all elements in an array and performs a calculation on each element while updating the value of an accumulator. However, while REDUCE returns a single value, SCAN returns an array of values. 

The SCAN function takes three arguments: initial_value, array, and lambda. Initial_value is the initial seed value to use for the first result. Initial_value is optional. Array is the array to scan and lambda is a custom LAMBDA function to perform on each element of array.

LAMBDA structure

SCAN uses the LAMBDA function to apply the required calculation. The LAMBDA is applied to each value, and the result from SCAN is an array of results with the same dimensions as the original array. The structure of the LAMBDA used inside of SCAN looks like this:

LAMBDA(a,v,calculation)

The first argument, a, is the accumulator used to create intermediate values. The accumulator begins as the initial_value provided to SCAN and changes as the SCAN function loops over the elements in the array and applies a calculation. The v argument represents the value of each element in the array. The calculation is a formula that refers to both the accumulator (a) and value (v). The result of the calculation determines the value of the accumulator at that point in the scan, and the final result from SCAN is an array that contains all accumulator values created during the scan.

Note: SCAN returns the value of the accumulator when each element in the array is processed. The result is an array of "intermediate" values. To process each element in an array individually and return an array of non-intermediate results, see the MAP function

Examples

In the formula below, SCAN is used to create a running total of an array with three values:

=SCAN(0,{1,2,3},LAMBDA(a,v,a+v)) // returns {1,3,6}

In the LAMBDA function, a is the initial_value given to SCAN (zero), and v represents the individual elements in array. The LAMBDA runs one time for each element in array, and at each iteration, the value of v is added to the accumulator. SCAN returns the array {1,3,6} as a final result. 

In the worksheet shown above, the formula in D5 is:

=SCAN(0,B5:B16,LAMBDA(a,v,a+v))

The result is a running sum of values in the range B5:B16.

Text values

To work with text values, set the initial_value to an empty string (""). The formula below creates a running concatenation of an array:

=SCAN("",{"a","b","c"},LAMBDA(a,v,a&v)) // returns {"a","ab","abc"}

Notice that because the incoming array has three values, the resulting array also has three values.