Forum Discussion

AmyM-NirR's avatar
AmyM-NirR
Copper Contributor
Jan 17, 2022
Solved

Max Running average

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 i...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jan 17, 2022

    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
)

     

    File attached.

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jan 17, 2022

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
)

 

File attached.

 

  • AmyM-NirR's avatar
    AmyM-NirR
    Copper Contributor
    Jan 17, 2022
    Brilliant!
    A little bit messy, but it does the trick.

    Thanks!
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 17, 2022

      AmyM-NirR 

      Similar variant

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

Resources