Forum Discussion

Copper Contributor

Jan 07, 2024

Rolling Average

Is there an easy way to get the rolling average? I am using the simple average formula but it is too manual.

MVP

Jan 07, 2024

To expand on Detlef_Lewin's reply:

Enter the following formula in F2:

=AVERAGEIFS($B$2:$B$10000, $A$2:$A$10000, E2)

This can be filled or copied downwards.

rosila0509
Copper Contributor
Jan 07, 2024
=averageifs will not work since on week 2 and 3, there will be two or more criteria already. the Week 2 result should be the average of all Week 1 and Week 2 values. and on week 3 it is should be the average of the values of Week 1, 2, and 3.
- Detlef_Lewin
  Silver Contributor
  Jan 07, 2024
  rosila0509
  If you would use numbers for your weeks instead of text:
  =AVERAGEIFS(Table1[Values],Table1[Week],"<="&D2)
- HansVogelaar
  MVP
  Jan 07, 2024
  rosila0509
  
  In F2:
  
  =AVERAGE($B$2:INDEX($B$2:$B$10000, XMATCH(E2, $A$2:$A$10000, 0, -1)))
  
  Fill down.
  - rosila0509
    Copper Contributor
    Jan 08, 2024
    HansVogelaar I am getting an error using this formula

Resources