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.

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

• Use the Fill Handle to copy and paste the formula downwards to get other CMAs.

• 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 MA–moving 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.

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

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

• Here is the final output.

Download Practice Workbook

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

Related Articles

• How to Calculate 7-Day Moving Average in Excel
• How to Calculate Exponential Moving Average in Excel
• How to Generate Moving Average in Excel Chart
• How to Calculate Running Average in Excel

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