The `REDUCE` function in Excel is part of the Dynamic Arrays functions and is primarily used to apply an accumulator function across a range, reducing it to a single value. This function is available in Excel 2021 and Microsoft 365 versions. Here’s a guide on how to use the `REDUCE` function:
Syntax
REDUCE([initial_value], array, lambda(accumulator, value))
- `initial_value`: This is the starting value for the accumulator. It is an initial seed that can be a number, string, or other types.
- `array`: The range or array of elements that you want to reduce.
- `lambda(accumulator, value)`: A `LAMBDA` function that specifies what operation to perform on each element of the array. This function is called repeatedly, and it needs two parameters:
- accumulator: The current accumulated value.
- value: The current value from the array being processed.
Example
Suppose you have a column of numbers in `A1:A5` and you want to sum these numbers using the `REDUCE` function.
=REDUCE(0, A1:A5, LAMBDA(acc, val, acc + val))
- Step-by-Step Example:
- You have numbers in the range `A1:A5`: `2`, `3`, `4`, `5`, and `1`.
- Set an `initial_value`, which will be `0` for summation.
- Use `REDUCE` to sum these numbers:
Explanation:
- `initial_value`: `0`, serves as our starting sum.
- `array`: `A1:A5`, the range of numbers to sum.
- `LAMBDA(acc, val, acc + val)`: Adds each element in the range to the accumulated sum.
More Complex Example
You can also use `REDUCE` for more complex operations, like concatenating strings.
=REDUCE("", B1:B5, LAMBDA(acc, val, acc & val))
Here, the function will concatenate all strings from the range `B1:B5`.
Tips
- Error Checking: Ensure that the `LAMBDA` function is well-defined and there’s no circular reference or miscalculation.
- Type compatibility: The type of `initial_value` should be compatible with operations in the `LAMBDA` function.
- Complex Operations: You can perform operations other than sum, such as product, concatenation, etc., by modifying the `LAMBDA` function accordingly.
By using `REDUCE`, you gain greater flexibility and can perform a wide variety of reduction operations in a single formula without needing additional helper columns or rows.
Unlock Your Potential
Excel
Access
Power BI
