**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 interva**l 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.**

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

`=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**.

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