The REDUCE function is used to reduce a given array to an accumulated value defined by the LAMBDA function. It is somewhat reminiscent of conditional data aggregation functions but provides far more possibilities. In the example that follows, you will be able to see its application for calculating the last day of the month when sales were higher than average.
MAP is used as an auxiliary function, in combination with the LAMBDA function, it is dynamic in nature and returns all those data range values that meet the specified condition. It reminds a little of other functions for working with dynamic arrays, but instead of values, it returns those that were calculated within a given expression. The LAMBDA function is in charge of the calculation.
LAMBDA is a new Excel function that allows easy creation of formulas, for one or more parameters. It works similarly to the LET function, but offers significantly greater capabilities. This feature was announced 2 years ago and only today it was included in the Microsoft365 update, so I’m looking forward to finally sharing with you the knowledge of how to use it. Let’s get to know the LAMBDA function!
One of the earlier “recipes” was about the INDIRECT function. It serves to forward a range as a parameter of SUM, AVERAGE, MAX, MIN or, for example, some other aggregation function. A range is defined as string that is contained in another cell. Let’s show, in one simple example, how this works.
This is a function that is used to aggregate – group data from a given range, which is done by specifying a parameter that calls a specific function (SUM, AVERAGE, COUNT, MAX, MIN, etc.). We can also perform aggregation using other functions, but this one is specific in that it offers various options that allow you to ignore nested functions, hidden rows, errors …