Excel REDUCE function

Summary

The Excel REDUCE function condenses an array into a single accumulated value by applying a specified LAMBDA function to each element of the array. It systematically combines each element with an accumulator—initially set to an optional starting value—through the specified operation, making it highly useful for cumulative calculations, custom aggregations, or iterative processes that apply across array elements.
Syntax
				
					=REDUCE([initial_value], array, lambda(accumulator, value))
				
			
  • [initial_value]: Optional. The starting value for the accumulator. If not specified, the first element of the array is used as the starting point.
  • array: The array of values to be reduced.
  • lambda: A LAMBDA function that takes two parameters: the current value of the accumulator and the current array element. It defines the operation to be performed on these values.
Return value
A single value that is the result of sequentially applying the LAMBDA function to accumulate a result from the array.

How to use

To use the REDUCE function, first specify the array to be condensed and an optional initial value for the accumulator. Follow this by defining a LAMBDA function that outlines the calculation on the array’s elements and the accumulator, enabling a cumulative result from these operations.

Examples

Simple REDUCE
Summing Squared Values: Calculating the sum of squared values in an array.
				
					=REDUCE(0, A1:C2, LAMBDA(a, b, a + b^2))

				
			
This formula squares each element of the array A1:C2 and sums these squared values, starting with an initial accumulator value of 0.
REDUCE for Custom "PRODUCTIF"
Multiplying Values Greater Than 50: Creating a custom function to multiply only those values in an array that are greater than 50.
				
					=REDUCE(1, Table2[Nums], LAMBDA(a, b, IF(b > 50, a * b, a)))

				
			
Starts with an accumulator of 1 and multiplies it by each value greater than 50 in the column “Nums” of “Table2”, effectively skipping values less than or equal to 50.
REDUCE to Count Even Values
Counting Even Numbers in an Array: Using REDUCE to count the number of even numbers.
				
					=REDUCE(0, Table4[Nums], LAMBDA(a, n, IF(ISEVEN(n), 1 + a, a)))

				
			
Initializes the accumulator to 0 and increments it by 1 for every even number in the column “Nums” of “Table4”, providing a count of even numbers.
REDUCE for Summation
Summing All Values in an Array: Demonstrating a basic summation across an array.
				
					=REDUCE(0, {1, 2, 3, 4, 5}, LAMBDA(a, b, a + b))

				
			
Sums the values in the array from 1 to 5, showing how REDUCE simplifies aggregating array elements.
REDUCE for Conditional Sum
Summing Even and Odd Numbers Separately: Calculating separate sums for even and odd numbers in an array.
For even numbers:
				
					=REDUCE(0, B5:B16, LAMBDA(a, b, IF(ISEVEN(b), a + b, a)))
				
			
For odd numbers:
				
					=REDUCE(0, B5:B16, LAMBDA(a, b, IF(ISODD(b), a + b, a)))
				
			
These formulas use REDUCE with a conditional check inside the LAMBDA to selectively sum even or odd numbers within the range B5:B16.

Additional Notes

  • REDUCE is a powerful function for performing operations that need to accumulate a result across an array. Its ability to apply complex conditions or calculations at each step makes it versatile for a wide range of data processing tasks.
  • Choosing the correct initial value is crucial for operations like multiplication, where the result significantly depends on the starting point of the accumulator.

Related Functions

Excel MAKEARRAY function

The Excel MAKEARRAY function generates arrays using lambda functions, offering flexibility in array creation and manipulation.

Excel BYROW function

The Excel BYROW function applies a lambda function to each row in a range or array, facilitating row-based operations and analysis.

Excel MAP function

The Excel MAP function applies a lambda function to each element in an array or range, enhancing data transformation capabilities.

Excel LAMBDA function

The Excel LAMBDA function creates custom functions without VBA, enabling complex calculations and reusable formula components.

Excel SCAN function

The Excel SCAN function cumulatively applies a lambda function across an array, generating an array of intermediate results.

Excel LET function

The Excel LET function assigns names to calculation results, simplifying formulas and improving performance by reducing repetition.

Content Navigation