How to use the REDUCE function

What is the REDUCE function?

The REDUCE function shrinks an array to an accumulated value, a LAMBDA function is needed to properly accumulate each value in order to return a total.

1. Introduction

What is an array?

An array in Excel is a group of values. The values may come from a worksheet but sometimes the are hard-coded into formulas using curly brackets like this: {5,2,6,1}

What is a LAMBDA function?

The LAMBDA function lets you build custom functions using only regular Excel functions, no VBA is needed. These custom functions based on the LAMBDA function are available only in your workbook.

You can also create recursive functions that before was only possible in User defined Functions and macros created in visual basic for applications (VBA).

Can the REDUCE function create more values than the original array?

Yes. You can actually create more values than the original size using the REDUCE function, this article shows how:
Workaround for the TEXTSPLIT function – LAMBDA function

2. REDUCE Function Syntax

REDUCE([initial_value], array, lambda(accumulator, value, calculation))

Back to top

3. REDUCE Function Arguments

[initial_value]	Optional. Accumulator start value.
array	Required. An array or cell range to be processed.
lambda( accumulator, value, calculation)	Required. A lambda function with three parameters: - accumulator - value - calculation
accumulator	Required. A value that changes for each value in the array or cell range.
value	Required. Iterates through each value in the array or cell range.
calculation	Required. A formula that uses parameters accumulator and value.

Back to top

4. REDUCE Function example 1

This example shows how to count values in a cell range that meet a specific condition. If a number begins with 1 the accumulator argument is increased by 1.

This is a simple demonstration of the REDUCE function, I know that the formula can be made a lot smaller using only the SUM function and the LEFT function. This an easy example so you can understand the basics of the REDUCE function.

Formula in cell D3:

The accumulator argument is used as a variable to keep track of numbers that begin with 1. For example, the first number in cell range B3:B12 is 745. It doesn't begin with 1 so nothing is added to the acc. The initial value of acc is 0 (zero) and is still 0 (zero).

However, the second value is 13 and it begins with 1. Acc is 0 (zero) and 1 is added to 0 (zero). The acc value is now 1. This repeats until all values in B3:B12 are processed.

Explaining the formula

Step 1 - Get first character from left

The LEFT function extracts a specific number of characters always starting from the left.

Function syntax: LEFT(text, [num_chars])

LEFT(val)

becomes

LEFT(745)

and returns "7".

Step 2 - Compare character with "1"

The equal sign lets you compare values in an Excel formula.

LEFT(val)="1"

becomes

"7"="1"

and returns FALSE.

The equal sign is a logical operator, the result is TRUE or FALSE.

Step 3 - Add acc by 1 if logical expression evaluates to TRUE

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(LEFT(val)="1",acc+1,acc)

becomes

IF(FALSE,acc+1,acc)

and returns acc which is 0 (zero) to begin with.

Step 4 - Pass two parameters to function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(acc,val, IF(LEFT(val)="1",acc+1,acc))

Step 5 - Pass all values in cell range B3:B12

REDUCE(0,B3:B12,LAMBDA(acc,val, IF(LEFT(val)="1",acc+1,acc)))

returns 4.

4 numbers begin "1" in cell range B3:B12.

Back to top

5. REDUCE Function example 2

This formula lists unique distinct "Regions"  from column C and populates the first column of the returning array, the remaining columns are populated by values from the "Text" column based on the adjacent "Region" value.

In other words, the formula groups values by items in a given column based on a condition that they are on the same row.

For example, item "East" is found in cells C3 and C6. The corresponding values on the same rows are "EVA LFO" and "THR DQY", these values are now grouped together in the output array. This is done with each unique distinct value in cell range C3:C12.

Formula in cell E3:

Explaining the formula

Step 1 - Compare Region to values in cell range C3:C12

The equal sign is a logical operator, the result is TRUE or FALSE. It lets you compare values in an Excel formula.

val=C3:C12

Step 2 - Filter values in cell range B3:B12 based on condition

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(B3:B12,val=C3:C12)

Step 3 - Transpose values vertically to horizontally

The TRANSPOSE function converts a vertical range to a horizontal range, or vice versa.

Function syntax: TRANSPOSE(array)

TRANSPOSE(FILTER(B3:B12,val=C3:C12))

Step 4 - Add array to acc

The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

Function syntax: VSTACK(array1,[array2],...)

VSTACK(acc,TRANSPOSE(FILTER(B3:B12,val=C3:C12)))

Step 5 - Create a LAMBDA function in order to use the REDUCE function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(acc,val,VSTACK(acc,TRANSPOSE(FILTER(B3:B12,val=C3:C12))))

Step 6 - List unique distinct values from cell range C3:C12

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(C3:C12)

becomes

UNIQUE({"East";"South";"South";"East";"North";"West";"South";"West";"South";"North"})

and returns

{"East";"South";"North";"West"}

Step 7 - Pass each unique distinct value to the LAMBDA function

REDUCE("",UNIQUE(C3:C12),LAMBDA(acc,val,VSTACK(acc,TRANSPOSE(FILTER(B3:B12,val=C3:C12)))))

First iteration: {"",#N/A,#N/A,#N/A}

All iterations stacked vertically:

{"",#N/A,#N/A,#N/A;"EVA LFO","THR DQY",#N/A,#N/A;"RQW WPQ","HNL RYN","UNW NUM","BHZ ONF";"HYJ WXA","JZM UXJ",#N/A,#N/A;"WEA HGU","NXE OGF",#N/A,#N/A}

The first row in the resulting array is a blank value and the remaining values are error values. This row is not needed.

Step 8 - Remove first row in the array

The DROP function removes a given number of rows or columns from a 2D cell range or array.

Function syntax: DROP(array, rows, [columns])

DROP(REDUCE("",UNIQUE(C3:C12),LAMBDA(acc,val,VSTACK(acc,TRANSPOSE(FILTER(B3:B12,val=C3:C12))))),1)

becomes

DROP({"",#N/A,#N/A,#N/A;"EVA LFO","THR DQY",#N/A,#N/A;"RQW WPQ","HNL RYN","UNW NUM","BHZ ONF";"HYJ WXA","JZM UXJ",#N/A,#N/A;"WEA HGU","NXE OGF",#N/A,#N/A},1)

and returns

{"EVA LFO","THR DQY",#N/A,#N/A;"RQW WPQ","HNL RYN","UNW NUM","BHZ ONF";"HYJ WXA","JZM UXJ",#N/A,#N/A;"WEA HGU","NXE OGF",#N/A,#N/A}

Step 9 - Stack unique distinct values and the resulting array horizontally

The HSTACK function combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

Function syntax: HSTACK(array1,[array2],...)

HSTACK(UNIQUE(C3:C12),DROP(REDUCE("",UNIQUE(C3:C12),LAMBDA(acc,val,VSTACK(acc,TRANSPOSE(FILTER(B3:B12,val=C3:C12))))),1))

returns

{"East", "EVA LFO", "THR DQY", #N/A, #N/A;"South", "RQW WPQ", "HNL RYN", "UNW NUM", "BHZ ONF";"North", "HYJ WXA", "JZM UXJ", #N/A, #N/A;"West", "WEA HGU", "NXE OGF", #N/A, #N/A}

Step 10 - Remove error values

The IFERROR function if the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.

Function syntax: IFERROR(value, value_if_error)

IFERROR(HSTACK(UNIQUE(C3:C12),DROP(REDUCE("",UNIQUE(C3:C12),LAMBDA(acc,val,VSTACK(acc,TRANSPOSE(FILTER(B3:B12,val=C3:C12))))),1)),"")

becomes

IFERROR({"East", "EVA LFO", "THR DQY", #N/A, #N/A;"South", "RQW WPQ", "HNL RYN", "UNW NUM", "BHZ ONF";"North", "HYJ WXA", "JZM UXJ", #N/A, #N/A;"West", "WEA HGU", "NXE OGF", #N/A, #N/A}, "")

and returns

{"East", "EVA LFO", "THR DQY", "", "";"South", "RQW WPQ", "HNL RYN", "UNW NUM", "BHZ ONF";"North", "HYJ WXA", "JZM UXJ", "", "";"West", "WEA HGU", "NXE OGF", "", ""}

Back to top

Back to top

'REDUCE' function examples

Functions in 'Logical' category

The REDUCE function function is one of 16 functions in the 'Logical' category.

Excel function categories

Excel categories

Leave a Comment