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

This article illustrates how we can calculate 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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Examples to Calculate Centered Moving Average in Excel

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.

How to Calculate Centered Moving Average in Excel

Problem: Calculate 3 days 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).

How to Calculate Centered Moving Average in Excel

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)

How to Calculate Centered Moving Average in Excel

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

How to Calculate Centered Moving Average in Excel

  • Now, we can use the Fill Handle to copy and paste the formula downwards to get other CMAs.

How to Calculate Centered Moving Average in Excel

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

How to Calculate Centered Moving Average in Excel

Read More: How to Calculate Moving Average in Excel (4 Examples) 


Similar Readings: 


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 even no of periods. The dataset contains sales data for the year 2021 with 12 periods.

Problem: Calculate 4 months 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.

How to Calculate Centered Moving Average in Excel

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.

How to Calculate Centered Moving Average in Excel

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

How to Calculate Centered Moving Average in Excel

Following this procedure, we’ll calculate CMAs for the whole dataset.

How to Calculate Centered Moving Average in Excel

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)

How to Calculate Centered Moving Average in Excel

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

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

How to Calculate Centered Moving Average in Excel

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

How to Calculate Centered Moving Average in Excel

  • Here is our final output.

Read More: How to Calculate Average in Excel (Including All Criteria)


Notes

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

How to Calculate Centered Moving Average in Excel

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

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo