Google Sheets Function: SCAN
The SCAN function traverses an array and produces intermediate values by applying a 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:
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):