In time series analysis, a centered moving average is the moving average of a certain number of values, centered around a specific period.
The following example shows how to calculate a centered moving average for a dataset in Excel.
Example: How to Calculate Centered Moving Average in Excel
Suppose we have the following dataset that shows the total sales made during 12 consecutive months by some company:
Suppose we would like to calculate a 3-month centered moving average of sales values.
To do so, we can type the following formula into cell C3:
=AVERAGE(B2:B4)
We can then click and drag this formula down to each remaining cell in column C:
The values in column C represent the 3-month centered moving average of the values in the sales column.
For example, the 3-month centered moving average of sales in February is 25.667.
The formula uses the sales values in January, February and March to calculate this average:
3-Month Centered Moving Avg. of Sales in February: (22+25+30) / 3 = 25.667
This matches the value calculated by our formula.
We can also use a different number of months to calculate the centered moving average values.
For example, we could type the following formula into cell C4 to calculate the 5-month centered moving average of sales:
=AVERAGE(B2:B6)
We can then click and drag this formula down to each remaining cell in column C:
The values in column C now represent the 5-month centered moving average of the values in the sales column.
For example, the 5-month centered moving average of sales in March is 28.
The formula uses the sales values in January, February, March, April and May to calculate this average:
5-Month Centered Moving Avg. of Sales in March: (22+25+30+34+29) / 5 = 28
This matches the value calculated by our formula.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate a Weighted Moving Average in Excel
How to Calculate a Cumulative Average in Excel
How to Calculate an Exponential Moving Average in Excel
