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

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Calculate Centered Moving Average in Excel

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

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.g., Sunday, Monday, and Tuesday in cell E7.

How to Calculate Centered Moving Average in Excel

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


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.

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 MA–moving 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 use 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: Calculate Moving Average for Dynamic Range in Excel


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.


Download Practice Workbook

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo