REDUCE

What it does

REDUCE(initial_value, array, lambda)

REDUCE folds an array into a single value by threading an accumulator through it, one cell at a time.

The rule:

1. The accumulator starts at initial_value.
2. The array is enumerated in row-major order.
3. For each cell, the callable is invoked with two arguments — the current accumulator and the cell's value — and its result becomes the new accumulator.
4. When the enumeration ends, the accumulator is the result.

Order matters here in a way it does not for MAP, BYROW, or BYCOL, and for two distinct reasons:

• Semantically, because the callable need not be associative or commutative. Building a delimited string, or subtracting each element in turn, gives different answers under a different order.
• Numerically, because floating-point addition is not associative. A left fold over an array of doubles is a specific summation algorithm with a specific error behaviour, and it is not the same algorithm SUM uses. REDUCE(0, range, LAMBDA(a,v,a+v)) and SUM(range) are not required to agree in the last bits, and expecting them to is a mistake worth naming explicitly.

Arguments

REDUCE(initial_value, array, lambda)

• initial_value — required. The accumulator's starting value. It is the first argument, not the last, which is the position most easily misremembered.
• array — required. The values to fold over.
• lambda — required, and always third. It takes exactly two parameters, in the order (accumulator, value).

The commonly misunderstood positions are the first argument and the callable's parameter order. Swapping (accumulator, value) for (value, accumulator) produces a lambda that runs without complaint and computes something else.

Try it

Quirks

Result and edge cases

Returns a single value of whatever kind the last invocation produced. Unlike the other helpers on this shelf, REDUCE does not return an array.

• Empty array. With nothing to fold, initial_value should be the answer. This is the degenerate case most likely to differ between implementations and is unverified here.
• Single-cell array. One invocation.
• Accumulator kind changes mid-fold. Nothing forbids it: a fold that starts with a number and concatenates text will have a text accumulator after the first step. The callable is responsible for coherence.
• Errors in the array. Delivered to the callable as ordinary argument values. Whether the fold continues past an error depends entirely on what the callable returns.
• Accumulator becomes an error. It is threaded onward like any other value unless the callable handles it, so one bad cell typically poisons the whole result — usually the desired behaviour, occasionally a surprise.

How to implement

Notes for implementers

2. Thread the accumulator by value and accept it before preparing the next argument. Preparing several argument slices ahead of time is the natural optimisation and it is incorrect here.
3. Do not assume the accumulator's kind is stable. Typing the fold to the initial value's kind will fail on the first callable that changes it.
4. State the empty-array answer. Returning initial_value is the mathematically obvious choice; make it explicit rather than falling out of a loop that never ran.
5. A fast path for all-numeric arrays must produce identical results to the general path, including the order of operations — otherwise the optimisation is observable.

Rust code

2 reduce_fn "REDUCE" (3, 3) [R "initial_value", R "array", R "lambda"] resume reduce_resume => reduce_start; |s, a, sv| { run(s, reduce_start(s, a, sv), sv) }

fn reduce_start(
    site: &Site<'_>,
    a: &[CalcValue],
    sv: &mut EvalServices<'_>,
) -> Step<Box<dyn KernelTask>> {
    reduce_task(site, a, sv, false)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• SCAN is REDUCE that keeps every intermediate accumulator instead of only the last, and returns an array shaped like the input. If you want to see the running total rather than the total, SCAN is the function.
• MAP transforms without accumulating; BYROW and BYCOL reduce along one axis. REDUCE is the only member of the set that collapses everything to one value.
• SUM, PRODUCT, CONCAT, TEXTJOIN are the built-in folds. Prefer them when they fit: they are faster and, for SUM, their summation behaviour is the one the rest of the workbook assumes.
• LAMBDA supplies the callable; recursion via a named lambda is the alternative route to iteration, and REDUCE is usually the clearer one.
• Readers confuse REDUCE with MAP (accumulating versus transforming) and with SCAN (final versus running).

Named variants

No named variants are recorded yet.