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

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

Calculate this dataset’s centered moving average for odd no periods. The dataset shows 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. 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.

Calculate the CMA for the rest of the dataset. Let’s move to the solution.

Solution:

• In cell E6, 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, respectively, the first three periods. Put the following formula in cell E6 and hit Enter.
`=AVERAGE(D5:D7)`

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

• We calculated the centered moving averages for the dataset successfully.

### Method 2 – Compute Centered Moving Average for Even No of Periods

We’re going to calculate the centered moving average for the following dataset with an even number of periods: 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.

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.

Calculate the average for the next 4 periods (2-5) and put the output in the middle of the period in a new row at period 3.5.

Calculate the centered average we’ll take an average of the averages that we calculated before.

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

We’ll get rid of the intermediate rows and the MAmoving average column.

Solution:

Solve the problem quickly to implement it 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.

• 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 the final output.

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