# How to Calculate Centered Moving Average in Excel (2 Examples)

This article illustrates how we can calculate a centered moving average in Excel with 2 step-by-step examples. The centered moving average is heavily used in data analytics to understand and visualize trends in business, weather forecasting, etc.

## How to Calculate Centered Moving Average in Excel: 2 Examples

In this article, weâ€™ll explain 2 different examples that we need to learn to understand the centered moving averages thoroughly. Before that, we need to know what the moving average is. It is an average of subsets of data at a specific interval. The selection of subsets keeps moving from the start to the end of the main dataset. This helps to understand the trend of a dataset removing irregularities.

### 1. Calculate Centered Moving Average for Odd No. of Periods

In this example, we want to calculate the centered moving average for odd no periods for this dataset. The dataset shows the data of the number of sales for a week for a product.

Problem: Calculate a 3-day centered moving average for the dataset.

Problem Analysis:Â  We need to calculate the moving average of the first 3 periods i.e., for Saturday, Sunday, and Monday. And we should put the value in the center of the time interval (shown in the screenshot).

In the next step, weâ€™ll estimate the CMA for periods no 2,3, and 4 i.e., for Sunday, Monday, and Tuesday. The calculated value should be placed in the middle of the time interval.

Following this procedure, we need to calculate the CMA for the rest of the dataset. Letâ€™s move to the solution.

Solution:

• In cell E6, which is the center for the time interval of the first three periods, we took the average of cells D5:D7. These cells represent the sales values for Saturday, Sunday, and Monday e., the first three periods respectively. Put the following formula in cell E6 and hit Enter.
`=AVERAGE(D5:D7)`

• Following the same procedure, we can calculate the centered average for the next three consecutive days e.g., Sunday, Monday, and Tuesday in cell E7.

• Finally, we have calculated the centered moving averages for the dataset successfully.

### 2. Compute Centered Moving Average for Even No of Periods

In this example, weâ€™re going to calculate the centered moving average for the following dataset for an even no of periods. The dataset contains sales data for the year 2021 with 12 periods.

Problem: Calculate a 4-month centered moving average for the dataset.

Problem Analysis: We need to calculate the centered moving average for the first 4 periods i.e. for months Jan, Feb, Mar, and Apr. But the issue is where to put the calculated value as we have even no periods. Letâ€™s break the problem into pieces to understand it better.

To solve this, weâ€™ll calculate the average of the first 4 periods (Jan – Apr) and put it in the center of the time interval. For better understanding, we inserted a new row as period 2.5.

After that, weâ€™ll calculate the average for the next 4 periods (2-5) and put the output in the middle of the time period in a new row at period 3.5.

Now, to calculate the centered average weâ€™ll take an average of the averages that we calculated before.

Following this procedure, weâ€™ll calculate CMAs for the whole dataset.

Finally, weâ€™ll get rid of the intermediate rows and the MAmoving average column.

Solution:

In this section, weâ€™ll solve the problem quickly so that we can implement that in our calculations.

• To get the average of 1st 4 periods put the following formula in cell E7.
`=AVERAGE(D5:D8)`

• Using the Fill Handle calculate the moving averages for the whole dataset.

• Now, in cell F7 use the following formula to calculate the first centered moving average.
`=AVERAGE(E7:E8)`

• Locate the Fill Handle at the bottom right corner of cell F7 and drag it down to cell

• Here is our final output.

## Notes

While calculating the moving averages, we stopped at cell E15. If we copied the formula further, it would still give us an output.

But as we can see from the above screenshot, the average value is for the last 3 periods, not 4. The same thing can happen in the case of calculating the CMAs. So, we need to be careful about this.

## Conclusion

Now, we know how to calculate centered moving averages in Excel. Hopefully, it would encourage you to use these techniques to solve your issues more confidently. Any questions or suggestions donâ€™t forget to put them in the comment box below.

## Related Articles

<< Go Back to Moving Average | Calculate Average in ExcelÂ |Â How to Calculate in ExcelÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF