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

`=AVERAGE(D5:D8)`

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

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