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.

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

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 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**e., the first three periods respectively. Put the following**formula**in cell**E6**and hit**Enter**.

`=AVERAGE(D5:D7)`

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

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

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

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

`=AVERAGE(D5:D8)`

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

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

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

- How to Calculate 7-Day Moving Average in Excel
- How to Calculate Exponential Moving Average in Excel
- How to Determine Triple Exponential Moving Average in Excel
- How to Generate Moving Average in Excel Chart
- How to Calculate Running Average in Excel
- Excel Formula to Find Displaced Moving Average

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