Excel functions / Logical
REDUCE function
Boils an array down to one result by running a function on a running value and each item in turn.
Syntax
=REDUCE(initial_value, array, lambda)
Arguments
initial_value: What the running value starts as. Left empty, it starts as the first item.array: The array to go through.lambda: A LAMBDA with two inputs: the running value, then the item.
When to use it
REDUCE runs a LAMBDA over a range one value at a time, carrying a running result from each step to the next, and gives the final result. It can build anything a loop would: a total with special rules, a joined text, a count of pairs.
Examples
Add up only the even numbers
A B C 1 Value 2 3 18 3 8 4 5 5 10 In C2:
=REDUCE(0, A2:A5, LAMBDA(total, x, IF(ISEVEN(x), total+x, total)))→ 18Join items with arrows
A B C 1 Item 2 Pen Start > Pen > Stapler > Folder 3 Stapler 4 Folder 5 Tape 6 Glue 7 Ruler In C2:
=REDUCE("Start", A2:A4, LAMBDA(path, x, path & " > " & x))→ Start > Pen > Stapler > Folder
Common mistakes
- The order of the LAMBDA's names. The first name is the running result, the second the current value.
Practise it
Related functions
- SCAN: Like REDUCE, but returns every step: a running total, a running maximum and the like.
- MAP: Runs a function on each value of an array and returns the results in the same shape.
- LAMBDA: Makes a function of your own: names for its inputs, then the calculation.
- SUMPRODUCT: Multiplies matching items in arrays, then adds the results.