This Excel LAMBDA module contains functions for calculating aggregates over rolling windows of a vector

excel-lambda-rolling.txt

/*
Module: rolling

Contains functions for calculating statistics over rolling windows of a vector.
*/

/*
Function: aggregate

Calculates a rolling aggregate of a variable 'x' over windows of size 'window'.

The aggregate applied is that function provided to 'function'.

Inputs:
  x: a single-column numerical variable, sorted in the order the user expects to calculate rolling calculations on
  window: an integer specifying the window length/width. For example, if window is 3, then the aggregate will be applied over the set of 3 rows ending in the current row
  function: a function that accepts a single array argument and returns a scalar
  
Returns:
  An array with ROWS(x) rows and 1 column containing the result of the aggregation over each window. For the first window-1 rows, the output array will show NA()

Usage:

For a sample of 100 rows, where some measurement of interest exists in B2:B101, and where we want
rolling aggregates over each window of three rows ending in the current row:

We configure the array windows:

=rolling.aggregate(B2:B101, 3)

Which returns the inner LAMBDA whose only argument is 'function'. 

So we can do this to get a sum of rolling windows of size 3:

=LET(
  windows, rolling.aggregate(B2:B101, 3),
  windows(SUM)
)

Or simply:

=rolling.aggregate(B2:B101, 3)(SUM)

Or, if we want other functions over the same windows, then:

=LET(
  windows, rolling.aggregate(B2:B101, 3),
  HSTACK(windows(SUM), windows(AVERAGE), windows(MAX), windows(MIN), etc)
)


*/

aggregate =LAMBDA(x,window,
  LAMBDA(function,
    LET(
      _i,SEQUENCE(ROWS(x)),
      MAP(_i,
        LAMBDA(b,
          IF(
              b < window,
              NA(),
              function(TAKE( DROP(x, b-window), window))
          )
        )
      )
    )
  )
);

/*
aggregate_using_index is the function shown in the accompanying video
https://youtu.be/z72a2997bOc?si=A9s5-x9-i_SND0UC
*/
aggregate_using_index =LAMBDA(x,window,
  LAMBDA(function,
    LET(
      _i,SEQUENCE(ROWS(x)),
      MAP(_i,
        LAMBDA(b,
          IF(
              b < window,
              NA(),
              function(INDEX(x, b - window + 1, 1):INDEX(x, b, 1))
          )
        )
      )
    )
  )
);