REDUCE function

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 initial accumulator value.
• array_or_range: An array or range to be reduced.
• LAMBDA: A LAMBDA that’s applied to each value in array_or_range to reduce it.
• Syntax: LAMBDA(name1, name2, formula_expression)
• Requirements:
  • The LAMBDA must have exactly 2 name arguments along with a formula_expression which uses those names. The name1 resolves to the current value in the accumulator and name2 resolves to the current_value in array_or_range, when applying the LAMBDA. The accumulator is updated in each step to the intermediate value obtained in the previous step. Go to simple multiplication operation example.

Notes

• The passed LAMBDA should accept exactly 2 name arguments, otherwise an #N/A error is returned. These arguments correspond to accumulator and current_value, in order. These are explained as:
  • name1: Resolves to the current value in the accumulator.
  • name2: Resolves to the current_value in the input array.
• The accumulator is initialized by initial_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 the LAMBDA is being applied.

• A named function can be passed for the LAMBDA parameter and behaves like a LAMBDA in this case. Learn more about named functions.
  • The named function must follow the LAMBDA syntax for REDUCE with exactly 2 argument placeholders defined for it.

  • Parenthesis shouldn't follow the named function.

Examples

Simple multiplication operation

Return the product of all elements in A1:A3 and initial_value.

Example data:

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:

Sum if price is greater than or equal to 20ドル

Add all the prices that are greater than or equal to 20ドル.

Example Data:

Example: =REDUCE(0, A1:A4, LAMBDA(accumulator, price, if(price>=20, accumulator + price, accumulator)))

Result:

Use a named function as LAMBDA function

Return the end price after increasing it by a certain percentage every period.

Make a Copy

Example Data:

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:

Use a named function as LAMBDA with 2-dimensional dataset

Find the list of unique employees of the quarter, preserving row-wise order.

Make a Copy

Example data:

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:

Common Errors

The passed LAMBDA doesn't have exactly 2 name arguments The last parameter of REDUCE wasn’t a LAMBDA The LAMBDA passed to REDUCE was incorrect

Related functions

• LAMBDA function: This function lets create and return a custom function with a set of names and a formula_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.