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.

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

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.

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

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

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

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

Similar Readings 

• How to Calculate 5 Star Rating Average in Excel (3 Easy Ways)
• Get Average Time in Excel (3 Examples)
• Calculate Moving Average for Dynamic Range in Excel (3 Examples)
• How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)
• Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)

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.

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

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

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

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

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

• How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)
• Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)
• Running Average: How to Calculate Using Excel’s Average(…) Function
• How to Calculate Percentage above Average in Excel (3 Easy Ways)
• Determine Triple Exponential Moving Average in Excel