Google Sheets Function: SCAN

The SCAN function traverses an array and produces intermediate values by applying a LAMBDA function.

Prerequisite: understand the LAMBDA function.

Usage:

=SCAN(initial_value, array_or_range, LAMBDA)


Example of use

The objective will be to complete the column containing the account balance for each month using a single formula:

google sheets account balance scan

Start by entering the SCAN function followed by the initial value, which here will be 0 (but could also have been a number corresponding to the previous year's balance, for example):

=SCAN(0

Then enter the range of cells containing the values to be processed:

=SCAN(0,B2:B13

Next, add the LAMBDA function and choose two names for the variables that will contain the cumulative result (balance) and the monthly amount (movements):

=SCAN(0,B2:B13,LAMBDA(balance,movements

And finally, enter the useful formula (which here is a simple addition):

=SCAN(0,B2:B13,LAMBDA(balance,movements,balance+movements))

The SCAN function will then start with a balance of 0, subsequently traverse the cells in the array B2:B13, add the movements of the current month, and return for each month the intermediate balance (in other words, the cumulative amount of the movements up to that month):

google sheets functions scan lambda
If needed, you can copy the Google Sheets document (or view the document) with this example.
The SCAN function is similar to the REDUCE function, but unlike REDUCE which returns only the final result, SCAN returns all the intermediate values that lead to the same final result.