Excel Function: SCAN

The SCAN function goes through each value in an array, applies a LAMBDA function, and returns an array of each intermediate value.

Prerequisite: understand the LAMBDA function.

Usage:

=SCAN(initial_value, array, LAMBDA)

Example of use

The goal here is to complete the column containing the account balance of each month using a single formula:

Enter the SCAN function followed by the initial value 0 (to start the year with a balance of 0):

=SCAN(0

Then enter the range that contains the values to process:

=SCAN(0,B2:B13

Add the LAMBDA function then choose the name of the variable for the cumulative result (balance) and that for the amount of the month (movements):

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

And finally, enter the useful formula for performing the addition:

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

The SCAN function then starts with a balance of 0, goes through the cells of the array B2:B13, adds the current month's movements, and returns for each month the intermediate balance (in other words, the accumulated amount of movements up to this month):

If needed, you can download the Excel file used here: scan.xlsx

The operation of the SCAN function is similar to that of the REDUCE function, but unlike REDUCE which only returns the final result, SCAN returns all the intermediate values that lead to this same final result.

Note: this function is only available with Office 365.