REDUCE function

The REDUCE function is used to reduce a given array to an accumulated value defined by the LAMBDA function. It is somewhat reminiscent of conditional data aggregation functions but provides far more possibilities. In the example that follows, you will be able to see its application for calculating the last day of the month when sales were higher than average.

The syntax of this function is:

=REDUCE ([<initial value>], <array>, <calculation>)

The first, optional argument, is the initial value. Then we list the LAMBDA function that is used to calculate the value.

If it is necessary to calculate the last value of sales in the table with given sales values ​​in a period of one month, when it was higher than the average, it is necessary to write the formula:

=REDUCE(,B3:B33,LAMBDA(x,y,IF(y>$E$2,y,x)))

Previously, the average value in cell E2 was calculated using the AVERAGE function. We could also use AVERAGE within the LAMBDA function but then the REDUCE function would work slower. Conditional formatting has been applied to the table so that you can more easily see the last value that meets the specified condition.