The SCAN function in Google Sheets is a powerful tool that allows users to iterate over an array while applying a specified LAMBDA function. This feature enables the generation of intermediate results, providing valuable insights into the array’s data as it processes each element. It is particularly useful for cumulative calculations and complex data manipulations.

Syntax

SCAN(initial_value, array, LAMBDA) 

• initial_value: The starting point for the scanning process. This value is the basis on which the subsequent calculations are built.
• array: The range or array of values to be processed. Each value will be used in conjunction with the LAMBDA function to produce intermediate results.
• LAMBDA: A user-defined function that specifies how to combine the initial value with each value from the array. It provides the logic for generating intermediate outputs.

Example #1

=SCAN(0, A1:A5, LAMBDA(prev, curr, prev + curr))

This function takes an initial value of 0 and iterates over the values in cells A1 through A5, summing each value with the previous sum. If A1 contains 1, A2 contains 1, A3 contains 1, A4 contains 1, and A5 contains 1, the result would be: 0, 1, 2, 3, 4, 5.

Example #2

=SCAN(1, B1:B5, LAMBDA(prev, curr, prev  curr))

Here, the function initializes at 1 and multiplies each value in the range B1 through B5 with the cumulative product. If B1 through B5 contains 2, 3, 4, 5, and 6, the result would be: 1, 2, 6, 24, 120, 720.

Example #3

=SCAN(0, C1:C5, LAMBDA(prev, curr, IF(curr > 0, prev + 1, prev)))

This example counts the number of positive values in the range C1:C5. If C1 through C5 contains -1, 2, 3, -4, and 5, the result would be: 0, 0, 1, 1, 2, 3.

Error handling

• VALUE!: This error occurs when the value provided in the array or the initial value is non-numeric or incompatible with the LAMBDA function’s operation.
• REF!: If the cell reference provided in the array does not exist, this error will appear, indicating that the cell reference is invalid.
• NUM!: This error indicates a problem with a calculation within the LAMBDA function, such as exceeding numerical limits or receiving an invalid argument.

Conclusion

The SCAN function is a versatile addition to Google Sheets that enhances the capability of data analysis and manipulation. By enabling users to generate intermediate values through a defined LAMBDA function, it facilitates cumulative calculations and complex logical operations, making it an essential tool for anyone looking to perform advanced data processing in their spreadsheets.