How do I use the REDUCE function in Google Sheets?

To use the REDUCE function in Google Sheets, you either type the formula directly into a cell or insert it via the function menu. The REDUCE function applies a LAMBDA function to each value in an array along with an accumulator, reducing the array to a single result.

Here's how:

1. Typing the Formula:

• In any cell, start typing =REDUCE(. Google Sheets will offer autocomplete suggestions.

2. Inserting via the Function Menu:

• Click on the cell where you want the formula.
• Go to Insert → Function → Array → REDUCE. This will insert the basic REDUCE formula structure into the cell.

3. Understanding the REDUCE Syntax:

The REDUCE function has the following syntax:

REDUCE(initial_value, array_or_range, LAMBDA(accumulator, current_value, formula_expression))

• initial_value: The starting value for the accumulator. This is the value that the accumulator will hold before processing the first element of the array.
• array_or_range: The array or range of cells to be reduced. This is the data you want to iterate over.
• LAMBDA(accumulator, current_value, formula_expression): A LAMBDA function that is applied to each element in the array_or_range.
  • accumulator: A name to refer to the accumulated value. This starts with the initial_value and gets updated in each iteration.
  • current_value: A name to refer to the current element being processed in the array.
  • formula_expression: The formula that calculates the new accumulated value based on the accumulator and the current_value.

Example:

Let's say you have a range of numbers in cells A1:A5 (e.g., 1, 2, 3, 4, 5) and you want to calculate their sum using REDUCE. You would use the following formula:

=REDUCE(0, A1:A5, LAMBDA(accumulator, current_value, accumulator + current_value))

• 0: The initial_value is 0 because we're starting the sum at zero.
• A1:A5: The array_or_range is the range containing the numbers.
• LAMBDA(accumulator, current_value, accumulator + current_value): This LAMBDA function takes the accumulator (initially 0) and adds the current_value (each number from A1:A5). The result becomes the new accumulator for the next iteration.

The result of this formula would be 15 (1+2+3+4+5).

Explanation:

The REDUCE function iterates through the range A1:A5.

1. Iteration 1: accumulator = 0, current_value = 1, formula_expression evaluates to 0 + 1 = 1. The accumulator is updated to 1.
2. Iteration 2: accumulator = 1, current_value = 2, formula_expression evaluates to 1 + 2 = 3. The accumulator is updated to 3.
3. Iteration 3: accumulator = 3, current_value = 3, formula_expression evaluates to 3 + 3 = 6. The accumulator is updated to 6.
4. Iteration 4: accumulator = 6, current_value = 4, formula_expression evaluates to 6 + 4 = 10. The accumulator is updated to 10.
5. Iteration 5: accumulator = 10, current_value = 5, formula_expression evaluates to 10 + 5 = 15. The accumulator is updated to 15.

The REDUCE function then returns the final value of the accumulator, which is 15.

The REDUCE function, in conjunction with LAMBDA, provides a powerful way to perform cumulative calculations and aggregations on arrays in Google Sheets. It allows you to apply custom logic to each element in an array and accumulate the results into a single value.