How to do Custom Mathematical Operations in an Array in Excel

Using REDUCE function with LAMBDA, we can do the job. The REDUCE function in Excel 365 Dynamic Array is used to reduce a range or array of values to a single value using a specified operation. It is similar to the SUM function, but allows for more complex operations such as finding the maximum or minimum value.

Syntax: =REDUCE(array, operation)

Here, operation can be one of the following:

• SUM: Returns the sum of the values in the array.
• PRODUCT: Returns the product of the values in the array.
• AVERAGE: Returns the average of the values in the array.
• MAX: Returns the maximum value in the array.
• MIN: Returns the minimum value in the array.

For example, we have a column with some numbers. If we want to get the sum of only even numbers in one cell and sum of odd numbers in another cell, we can do it by using the REDUCE function:

 

Fig.1: For Even Numbers

Fig. 2: For Odd Numbers

Note that the REDUCE function can only be used with the Excel 365 Dynamic Array version, as it returns an array of values rather than a single value.