Statistical
Moving Average (Rolling Average)
Calculate a moving (rolling) average that smooths out fluctuations in data by averaging the last N data points. Commonly used for trend analysis in sales, stock prices, and metrics.
Excel Formula
=AVERAGE(OFFSET(A2,-B$1+1,0,B$1,1))Step-by-Step Explanation
1
OFFSET creates a dynamic range of the last N values
2
A2 is the current data point
3
B$1 contains the window size (e.g., 3, 7, 30)
4
-B$1+1 moves the start point back N-1 rows
5
AVERAGE calculates the mean of those N values
6
Copy down to create the rolling calculation
Example
| Month | Sales (A) | 3-Month Avg (B) |
|---|---|---|
| Jan | $10,000 | — |
| Feb | $12,000 | — |
| Mar | $11,000 | $11,000 |
| Apr | $14,000 | $12,333 |
| May | $13,000 | $12,667 |
Result: =AVERAGE(A2:A4) for first average, then drag down
Common Variations
Simple N-period
=AVERAGE(A2:A4)Fixed 3-period average (adjust range)
Weighted moving average
=SUMPRODUCT(A2:A4,{1,2,3})/SUM({1,2,3})Recent values weighted more
Exponential moving average
=B1+(2/(N+1))*(A2-B1)EMA with smoothing factor
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →