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.
Table of Contents
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))
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. |
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.
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", "", ""}
'REDUCE' function examples
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]
The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]
Functions in 'Logical' category
The REDUCE function function is one of 16 functions in the 'Logical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form