How to Use the REDUCE and LAMBDA Functions in Google Sheets for Cumulative Calculations

Google Sheets offers powerful array formulas like ARRAYFORMULA and MAP, which excel at transforming rows or columns independently in a parallel, element-by-element manner. However, when a calculation requires maintaining state from previous elements—such as computing running balances, string concatenations with complex delimiter rules, compound interest progressions, or custom inventory decumulation—independent array operations fall short. The REDUCE function, paired with a custom LAMBDA expression, introduces true stateful folding into Google Sheets. By traversing an array sequentially and updating an accumulator variable at each step, REDUCE enables sophisticated cumulative logic within a single cell expression.

Syntax and Architecture of the REDUCE Function

The REDUCE function requires three core components: an initial accumulator value, a source array, and an iterating LAMBDA function:

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

• initial_value: The starting value assigned to the accumulator before processing begins. This can be a number (such as 0), an empty string (""), or a reference to an initial account balance.
• array_or_range: The range of cells or dynamic array through which REDUCE iterates sequentially.
• LAMBDA(accumulator, current_value, …): A custom anonymous function accepting two mandatory arguments:
  • accumulator: The running value carried forward from the previous iteration.
  • current_value: The value of the specific item currently being evaluated from the array.
  • formula_expression: The calculation that modifies and returns the new accumulator state for the subsequent step.

Performing Cumulative Text Concatenation with Delimiters

A classic application of REDUCE is string accumulation where formatting depends dynamically on prior values. To join non-empty tags from range A2:A10 separated by hyphens without generating trailing or leading punctuation:

=REDUCE("", A2:A10, LAMBDA(acc, val, 
  IF(val = "", acc, IF(acc = "", val, acc & " - " & val))
))

During the first step, acc starts empty. If cell A2 contains “Alpha”, the expression evaluates to “Alpha”. On the next non-empty cell “Beta”, acc already holds “Alpha”, producing “Alpha – Beta”. Empty cells are skipped entirely without creating double delimiters.

Calculating Compound Growth and Discount Cascades

Financial calculations that apply sequential percentage adjustments benefit tremendously from REDUCE. Suppose an initial investment of £10,000 in cell B1 experiences annual variable growth rates listed in cells C2:C6 (e.g., 0.05, -0.02, 0.08, 0.03, 0.06):

=REDUCE(B1, C2:C6, LAMBDA(capital, rate, capital * (1 + rate)))

In this clean expression, REDUCE sets capital to £10,000, multiplies it by 1.05 at year one, feeds the resulting £10,500 forward to year two, and compounds each rate sequentially, outputting the final portfolio balance without requiring multi-row balance tables.

Building Custom Running Totals Using SCAN vs REDUCE

When implementing iterative logic in Google Sheets, it is vital to distinguish between REDUCE and its sibling helper function SCAN:

• REDUCE: Consolidates the entire array into a single scalar result. It only outputs the final accumulated value after the last element is processed.
• SCAN: Follows identical syntax and argument structure, but outputs an expanded array displaying the intermediate accumulator state at every single step. If you require a complete running total column displayed down the sheet, use SCAN; if you require the concluding total, use REDUCE.

Debugging and Troubleshooting REDUCE Formulas

When building advanced REDUCE formulas, keep these guidelines in mind:

• Type Consistency: Ensure the initial_value matches the anticipated output type. Passing a numeric 0 when performing string concatenation can lead to unexpected 0Value prefixes if not guarded by logic.
• Handling Large Arrays: While REDUCE executes in memory, processing arrays with tens of thousands of rows using complex nested logic can approach Google Sheets calculation timeout limits. Filter the input range first using FILTER or QUERY to process only relevant rows.