REDUCE function

The REDUCE function in Excel is a dynamic array function introduced in Excel 365 that reduces an array to a single value by applying a function or operation across the elements of the array. This function allows you to create custom aggregations or calculations over arrays, and it can be particularly useful when you need to perform calculations like sums, averages, or more complex reductions.

Syntax:

=REDUCE(initial_value, array, LAMBDA(accumulator, current_value, formula))

• initial_value: This is the starting value (or accumulator) that will be used in the reduction process. It can be a numeric value or another type of data depending on what you’re calculating.
• array: The array (or range) over which the reduction will occur.
• LAMBDA(accumulator, current_value, formula): A custom function defined using LAMBDA, which takes two parameters:
  • accumulator: The result of the previous iteration (or the initial value in the first iteration).
  • current_value: The current element of the array being processed.
  • formula: The operation to be applied to both the accumulator and the current value.

Example 1: Summing an Array

Suppose you have an array {1, 2, 3, 4} and you want to sum the elements. You can use REDUCE as follows:

=REDUCE(0, A1:A4, LAMBDA(acc, x, acc + x))

• initial_value is 0 (the starting sum).
• The array is A1:A4 (which contains {1, 2, 3, 4}).
• The LAMBDA function sums the current value x to the accumulator acc.

This will return 10 as the sum of the array.

Example 2: Finding the Product of an Array

If you have an array {2, 3, 4} and you want to find the product of these values, use:

=REDUCE(1, A1:A3, LAMBDA(acc, x, acc * x))

• initial_value is 1 (since multiplying by 1 doesn’t affect the result).
• The array is A1:A3 (which contains {2, 3, 4}).
• The LAMBDA function multiplies each number in the array by the accumulator.

This will return 24 as the product of the array.

Example 3: Concatenating Text

You can also use REDUCE to concatenate text values. For example, if A1:A3 contains {"Hello", " ", "World"}, use:

=REDUCE("", A1:A3, LAMBDA(acc, x, acc & x))

• initial_value is "" (an empty string, which is the starting value).
• The array is A1:A3 (containing {"Hello", " ", "World"}).
• The LAMBDA function concatenates the current string x to the accumulator acc.

This will return "Hello World".

Use Cases:

• Summing or multiplying arrays: Commonly used for basic arithmetic operations like sum, product, etc.
• Concatenating text values: Useful for combining strings or texts in an array.
• Custom aggregations: You can perform more complex calculations that standard functions (like SUM or PRODUCT) cannot do.

Benefits:

• Flexibility: With REDUCE, you can define any custom operation to apply across the elements in an array.
• Dynamic arrays: It works with Excel’s dynamic arrays, so the result will “spill” if necessary and update automatically as the data changes.

The REDUCE function is especially powerful for complex operations where a built-in function might not suffice.