Site icon Kaizen.Personal computer work.

REDUCE Function. Cumulative calculation of a cell range.(Microsoft Excel)

Japanese version.

The REDUCE function is a new function added to Excel in February 2022.
It is an extension of the LAMBDA function and performs a cumulative calculation of any formula.

How it works

=REDUCE(initial_value, array, lambda(accumulator, value))

NameOmissionExplanation
initial_value0Initial values for cumulative calculations.
arrayRequired argument.
Cell range to be calculated.

Demonstrate

In the following example, the initial value is set to 10,000 and the values of A2 through A4 are gradually added.

The first argument of the LAMBDA function is the previous cell value, and the second argument is the current cell value.

=REDUCE(10000,A2:A4,LAMBDA(total,value,total+value))

Multiplication is also possible.

Cumulative results of changes and compound interest calculations are also possibl.although there is a FVSCHEDULE function for compound interest calculations.

=REDUCE(10000,D2:D4,LAMBDA(total,value,total*(1+value)))

These could be done by creating intermediate cells, but
This is useful when you want to avoid this and keep formulas in a single cell.
They are useful in situations where formulas are easily broken, especially for books that are network-shared and have many updaters.

Difference between SCAN and REDUCE functions.

The SCAN and REDUCE functions both perform cumulative calculations, but the results of the formulas are different.

The SCAN function retrieves a range of cells, including intermediate calculations, while the REDUCE function retrieves only the last result.

---

Links

Microsoft Excel Functions Logical

LAMBDA function.

Examples of Use

Formulas to remove multiple types of characters from cell text.

How to replace multiple texts with the SUBSTITUTE function.

Exit mobile version