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
1. Processing cell A1:
|
After processing cell A1:
|
2. Processing cell A2:
|
After processing cell A2:
|
3. Processing cell A3:
|
After processing cell A3:
|
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:
A |
B |
C |
|||
---|---|---|---|---|---|
1 |
Q1 |
Q2 |
Q3 |
Q4 |
|
2 |
2020 |
John |
Adam |
Stacy |
Adam |
3 |
2021 |
Peter |
Maurice |
John |
Kimberly |
4 |
2022 |
Stacy |
Michael |
Peter |
Adam |
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 argumentsIf 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.
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.
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 function: This function lets create and return a custom function with a set of
names
and aformula_expression
that uses them. - MAP function: This function maps each value in the given arrays to a new value.
- BYROW function: This function groups an array by rows.
- BYCOL function: This function groups an array by columns.
- SCAN function: This function scans an array and produces intermediate values.
- MAKEARRAY function: 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
.