A to Z of Excel Functions: The REDUCE Function
19 August 2024
Welcome back to our regular A to Z of Excel Functions blog. Today we look at the REDUCE function.
The REDUCE function
This function reduces an array to an
accumulated value by applying a LAMBDA function to each value and returning the total value
in what is known as the accumulator.
Its syntax is as follows:
REDUCE([initial_value], array, lambda)
where:
- initial_value: this is an optional argument and represents the starting value for the accumulator, i.e. the “running total” prompted by the lambda expression
- array: this is a required value and represents the array to be reduced
- lambda: this is also a required value and represents a LAMBDA function called to reduce the array, that consists of two parameters:
- accumulator: the returned (aggregated) value from LAMBDA
- value: a value from array.
Consider the table of average temperatures in Melbourne (Australia) given by the Excel Table Temps:
We could count how many months in the 10-year period had an average temperature between 15 and 20 degrees Celsius as follows:
=REDUCE(0,
Temps, LAMBDA(accumulator, value,
IF(AND(value >= 15, value <= 20), 1 + accumulator, accumulator)))
For each element of the Temps Table, defined by the LAMBDA function as value, the IF statement tests whether the temperature is between 15 and 20 degrees Celsius:
AND(value >= 15, value <= 20)
If this is true, one gets added to the running total (accumulator), so that a count is maintained. The first argument of REDUCE – zero [0], the optional argument – simply specifies the starting value (initial_value) for the accumulator, which must be zero in order for the count to make sense.
Again, we could create a second array which performs a corresponding check for each cell and count the TRUE values, but this formula reduces the workload (i.e. it reduces the array of values to just one value by making use of the specified LAMBDA), allowing the computation to be performed once again without any helper stages.
We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day. A full page of the function articles can be found here.
