Google Sheets Function: REDUCE
The REDUCE function reduces an array to a cumulative result by applying a LAMBDA function.
Usage:
=REDUCE(initial_value, array_or_range, LAMBDA)
Example of use
The objective will be to create the equivalent of a SUM function with REDUCE:
Start by entering the REDUCE function followed by the initial value of the accumulator (which will be 0 in the case of calculating a sum):
=REDUCE(0
Then enter the range of cells containing the values to be accumulated:
=REDUCE(0,B2:B9
Next, add the LAMBDA function and choose two names for the variables that will contain the cumulative result (total) and the current value (score):
=REDUCE(0,B2:B9,LAMBDA(total,score
And finally, enter the useful formula (which here is a simple addition):
=REDUCE(0,B2:B9,LAMBDA(total,score,total+score))
The REDUCE function will then start with a total set to 0, subsequently traverse the scores in the array B2:B9, and add each score to the total.
And finally, REDUCE will return the final total (which has accumulated all the values in the array B2:B9):
This is a simple sum, but for example, one could decide to add even numbers and subtract odd numbers.
In this case, the IF and ISEVEN functions would need to be added to the formula:
=REDUCE(0,B2:B6,LAMBDA(total,score,total+IF(ISEVEN(score),score,-score)))