SOLVED

Max Running average

Copper Contributor

Hi, Excel 365 user over here.

I'm looking for a way to use dynamic array formulas to calculate the max of average sum, in one formula. Below is an example of max running average, where the average is for a period of every four consecutive cells:

AmyMNirR_0-1642403211644.png

I'm intending to use the FILTER function on the original data, thus I can't use the OFFSET function to calculate the max running average. How can I do so?

 

I thought about using the SEQUENCE function combined with ROW() function to create a dynamic array of running average (i.e. average of cells 1-2-3-4, 2-3-4-5, 3-4-5-6 etc. in the dynamic array of the original data), but unfortunately I wasn't successful.

 

Thanks everyone! 

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@AmyM-NirR I've used LET, SEQUENCE, TRANSPOSE and MMULT to accomplish this particular task. Not very straight-forward but it works.

=LET(
    n, 4,
    array, myData,
    r, SEQUENCE(ROWS(array)),
    c, SEQUENCE(,ROWS(array)-n+1),
    mx, TRANSPOSE((r<=c+3)*(c<=r)),
    result, MMULT(mx,array)/n,
    result
)

Riny_van_Eekelen_0-1642416672580.png

 

File attached.

 

Brilliant!
A little bit messy, but it does the trick.

Thanks!

@AmyM-NirR 

Similar variant

=MMULT( INDEX( myData, SEQUENCE( ROWS(myData) - 4 + 1) + SEQUENCE(,4,0) ), SEQUENCE(4,,1,0) ) / 4
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@AmyM-NirR I've used LET, SEQUENCE, TRANSPOSE and MMULT to accomplish this particular task. Not very straight-forward but it works.

=LET(
    n, 4,
    array, myData,
    r, SEQUENCE(ROWS(array)),
    c, SEQUENCE(,ROWS(array)-n+1),
    mx, TRANSPOSE((r<=c+3)*(c<=r)),
    result, MMULT(mx,array)/n,
    result
)

Riny_van_Eekelen_0-1642416672580.png

 

File attached.

 

View solution in original post