# Rolling Average

Copper Contributor

# 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

# Re: Rolling Average

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!

# Re: Rolling Average

Use AVERAGEIFS().

# Re: Rolling Average

Enter the following formula in F2:

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

This can be filled or copied downwards.

# Re: Rolling Average

=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.

# Re: Rolling Average

In F2:

=AVERAGE(\$B\$2:INDEX(\$B\$2:\$B\$10000, XMATCH(E2, \$A\$2:\$A\$10000, 0, -1)))

Fill down.

# Re: Rolling Average

``=AVERAGEIFS(Table1[Values],Table1[Week],"<="&D2)``

# Re: Rolling Average

@HansVogelaar I am getting an error using this formula

# Re: Rolling Average

If you use comma as decimal separator (for example, two-and-a-half is written as 2,5) then you should use a semicolon ; to separate the arguments in the formula:

=AVERAGE(\$B\$2:INDEX(\$B\$2:\$B\$10000; XMATCH(E2; \$A\$2:\$A\$10000; 0; -1)))

If that doesn't help: what is the error you experience?