This function reduces an array to an accumulated result by application of a LAMBDA
function to each value.
Sample Usage
REDUCE(5, A1:A3, LAMBDA(accumulator, current_value, accumulator+current_value))
REDUCE(2, A1:A3, LAMBDA(accumulator, current_value, accumulator*current_value))
Syntax
REDUCE(initial_value, array_or_range, LAMBDA)
initial_value
: The initialaccumulator
value.array_or_range
: An array or range to be reduced.LAMBDA
: ALAMBDA
that’s applied to each value inarray_or_range
to reduce it.- Syntax:
LAMBDA(name1, name2, formula_expression)
- Requirements:
- The
LAMBDA
must have exactly 2name
arguments along with aformula_expression
which uses thosenames
. Thename1
resolves to the current value in theaccumulator
andname2
resolves to thecurrent_value
inarray_or_range
, when applying theLAMBDA
. Theaccumulator
is updated in each step to the intermediate value obtained in the previous step. Go to simple multiplication operation example.
- The
Notes
-
The passed
LAMBDA
should accept exactly 2name
arguments, otherwise an#N/A
error is returned. These arguments correspond toaccumulator
andcurrent_value
, in order. These are explained as:name1
: Resolves to the current value in theaccumulator
.name2
: Resolves to thecurrent_value
in the input array.
-
The
accumulator
is initialized byinitial_value
and updated in each step to the intermediate value obtained in the previous step. -
The
current_value
in the input array are found row by row, while theLAMBDA
is being applied. -
A
named function
can be passed for theLAMBDA
parameter and behaves like aLAMBDA
in this case. Learn more about named functions.- The
named function
must follow theLAMBDA
syntax forREDUCE
with exactly 2 argument placeholders defined for it. - Parenthesis shouldn’t follow the
named function.
- The
Examples
Simple multiplication operation
Return the product of all elements in A1:A3 and initial_value
.
Example data:
A | |
---|---|
1 | 3 |
2 | 2 |
3 | 4 |
Example: =REDUCE(5, A1:A3, LAMBDA(accumulator, current_value, accumulator*current_value))
How it works:
Initially, accumulator
= 5
- Processing cell A1:
accumulator = 5 current_value = 3 |
After processing cell A1:
accumulator = (return value of LAMBDA )= 5*3= 15 |
- Processing cell A2:
accumulator = 15 current_value = 2 |
After processing cell A2:
accumulator = (return value of LAMBDA )= 15*2= 30 |
- Processing cell A3:
accumulator = 30 current_value = 4 |
After processing cell A3:
accumulator = (return value of LAMBDA )= 30*4= 120 |
Result:
120 |
Sum if price is greater than or equal to $20
Add all the prices that are greater than or equal to $20.
Example Data:
A | |
---|---|
1 | $50 |
2 | $10 |
3 | $30 |
4 | $20 |
Example: =REDUCE(0, A1:A4, LAMBDA(accumulator, price, if(price>=20, accumulator + price, accumulator)))
Result:
$100 |
Use a named function as LAMBDA function
Return the end price after increasing it by a certain percentage every period.
Example Data:
A | B | C | |
---|---|---|---|
1 | 2022 | 10% | Starting Price: |
2 | 2023 | 5% | $100 |
3 | 2024 | 5% | |
4 | 2025 | 10% |
Example: =REDUCE(C2,B1:B4,PRICE_INCREASE)
Named function: PRICE_INCREASE
is a named function
which outputs the result after increasing by the percentage value in column B.
Formula definition: =accumulator+accumulator*cell
where accumulator
and cell
are argument placeholders defined for PRICE_INCREASE
.
Result:
133.4 |
Use a named function as LAMBDA with 2-dimensional dataset
Find the list of unique employees of the quarter, preserving row-wise order.
Example data:
1 | Q1 | Q2 | Q3 | Q4 |
---|---|---|---|---|
2 | 2020 | John | Adam | Stacy |
3 | 2021 | Peter | Maurice | John |
4 | 2022 | Stacy | Michael | Peter |
Named function: ADD_IF_NOT_PRESENT
is a named function
which adds a given string value to an array of values.
Function definition: =IF(CONTAINS(new_value, existing_values), existing_values, {existing_values, new_value})
, where existing_values
and new_value
are argument placeholders defined for ADD_IF_NOT_PRESENT
in that order, and CONTAINS
is another named function
.
Example: =REDUCE({B2}, B2:E4, ADD_IF_NOT_PRESENT)
Result:
John | Adam | Stacy | Peter | Maurice | Kimberly | Michael |
Common Errors
The passed LAMBDA doesn’t have exactly 2 name arguments
If the LAMBDA
function doesn’t have 2 name
arguments, this error occurs:
“Wrong number of arguments to LAMBDA. Expected 3 arguments, but got 2 arguments.”
Example: =REDUCE(5, C1:C4, LAMBDA(current_value, current_value+1))
In this example, LAMBDA
was given only 1 name
argument when it needed 2.
The last parameter of REDUCE wasn’t a LAMBDA
If the last parameter of REDUCE
function wasn’t a LAMBDA
function, this error occurs:
“Argument must be a LAMBDA.”
Example: =REDUCE(5, C1:C4, 3)
In this example the last function is 3
, instead of a LAMBDA
function.
The LAMBDA passed to REDUCE was incorrect
If 1 or more name
arguments aren’t valid, this error occurs:
“Argument 1 of function LAMBDA is not a valid name.”
Example: =REDUCE(5, C1:C4, LAMBDA(C1, v, C1+v))
In this example, C1
is an invalid name
since it clashes with a range.
Related functions
- LAMBDA: This function lets create and return a custom function with a set of
names
and aformula_expression
that uses them. - MAP: This function maps each value in the given arrays to a new value.
- BYROW: This function groups an array by rows.
- BYCOL: This function groups an array by columns.
- SCAN: This function scans an array and produces intermediate values.
- MAKEARRAY: This function creates a calculated array of specified dimensions.
- Create & use named functions: This function lets you create and store custom functions, similar to
LAMBDA
.