REDUCE Function. Cumulative calculation of a cell range.(Microsoft Excel)
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))
| Name | Omission | Explanation |
|---|---|---|
| initial_value | 0 | Initial values for cumulative calculations. |
| array | Required 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.
---