Forum Discussion
Rolling Average
Is there an easy way to get the rolling average? I am using the simple average formula but it is too manual.
8 Replies
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.
- =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.
If you would use numbers for your weeks instead of text:
=AVERAGEIFS(Table1[Values],Table1[Week],"<="&D2)
- Use AVERAGEIFS().
- I recently found a simple way to calculate a rolling average in Microsoft Excel, and I thought I'd share it with you all. This method uses the AVERAGE function and relative references. Here's how you can do it: Enter Your Data: Put your data in column A (A1 to A100, for example). Calculate Rolling Average: In the cell where you want the rolling average to start (let's say, B3), enter the formula: =AVERAGE(A2:A4) Drag this formula down for the entire column. Adjust the Formula for Your Needs: If your data starts from a different row, adjust the cell references accordingly. If you want a different rolling window (e.g., 4 cells instead of 3), modify the range accordingly. Now, your rolling average will update automatically as you add or modify data in column A. Feel free to ask if you have any questions or need further clarification! Happy Excel-ing!
