We often need to calculate the centered moving average values for prediction and forecasting in the real world. This article will introduce you with two basic ways in which you can calculate the centered moving average values in Excel for odd and even target length of cell ranges.

## ๐ Download Excel File

Download the Excel file below.

## Comparison Between Moving Average and Centered Moving Average

A moving average is the average of consecutive observations or values in a series. It is a statistic that finds out the average change in data over a certain period of time. We can calculate this kind of average by finding the averages of a series of subsets of the full data set. For example, a set of numbers include the digits 2,4,6,8,10,12,14,18. If the moving average length is 3, then the first moving average will be the average of 2,4, and 6. The second moving average will be the average of 4,6 and 8.

The centered moving average refers to the fact that we need to place the moving average values at the center of the range or the time period. For the odd time periods, it is possible easily. But for an even time period, we need to take one more step and smoothen the average values by** calculating the average** of two consecutive moving average values.

## Learn to Calculate Centered Moving Average in Excel with These 2 Examples

To demonstrate things easily, we consider a dataset where we present different products and their total sales across different quarters over the last three years in a table. Our goal is to calculate the centered moving average for this set of data in Excel for two cases. One case is when the number of quarters is odd and the other case is when the number of quarters is even. The dataset is presented below.

**Example**

### 1. For Odd Number of Quarters

Suppose, we will consider the target length of the moving average as 3 quarters in this example. So, the moving average will calculate the averages of consecutive 3 entries within the dataset. Since the target length is odd, the average value will be placed in the middle of the three values that are being averaged. The steps for this approach are as follows:

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- At first, we select cell
**E7**from the Centered Moving Average Column. - After that, we write the following formula in the formula bar. This calculates the average of the values in the cell range
**D6:D8**using**Average function**.

`=AVERAGE(D6:D8)`

- Next, we do the same for the next three consecutive set of data, that is for the entries in the cell range
**D7:D9**. - To do so, we select cell
**E8**and write the following formula.

`=AVERAGE(D7:D9)`

- Now, we donโt have to do the same thing for every 3 groups of data. We hold the
**Fill Handle**in cell**E8**. - We drag the
**Fill Handle**up to cell**E16**. We should note that we didnโt drag the**Fill Handle**up to cell**E17**because**E16**finds the average of the last remaining data in groups of 3 on the sale of products column.

- The centered moving averages will look like the following table:

**๐ Read More: ****5 Ways to Calculate 7-Day Moving Average in Excel**

**Example**

### 2. For Even Number of Quarters

This approach is rather a complicated one. Unlike the previous process, in this approach, the average that we can find across an even target length cannot be placed in the center of the even values. In this approach, we will use a target length of 4 for an even number of quarters, that is we will use all the 4 quarters of a year to find out the centered moving average. To find the place where we can put the centered moving average value, we can take the help of additional rows to give us a clear concept of this topic. Firstly, we can include fractional numbers in the quartersโ column.

Suppose that at first, we need to find the moving average of the first 4 quarters. Where will we place the value? In the row of quarter 2 or quarter 3? To be sure, we introduce a row with a quarter value of 2.5. We place the moving average value on that row.

Next, for the next consecutive 4 data comprised of quarters 2,3,4,1 will have an average that cannot be placed in either quarter 3 or quarter 4 row. So, an additional row of quarter 2.5 is introduced.

We do this for all the data. After that, we find the average moving average values obtained in the first quarter 2.5 and the second quarter 2.5 to smoothen the value which will have its place in the row of quarter 3. The next value will have its place on the row of quarter 4. In this way, we can find the centered moving averages which can be easily understood from the following diagram.

Now we look at the following steps in specific to calculate the centered moving average for an even number of quantities.

โฌ๏ธโฌ๏ธ **STEPS **โฌ๏ธโฌ๏ธ

- Now, we will implement the ideas explained so far. First, we select cell
**E8**in the row of quarter 3. - After that, we write the following formula in the formula bar.

`=AVERAGE(D6:D9)`

- Then we select the cell
**E9**and write the following formula for the next 4 consecutive data.

`=AVERAGE(D7:D10)`

- We drag the Fill Handle of cell
**E9**and drag it up to cell**E16**. We should note that we didnโt drag the Fill Handle up to cell**E17**because cell**E16**finds the average of the cell range**D14:D17**where**D17**is the last remaining data in the sale of products column.

- Since the averages found are not centered, we introduce another column and select the cell
**F8**in that column. - Then we find the average of
**E8**and**E9**by writing the following formula in the formula bar.

`=AVERAGE(E8:E9)`

- Then we select the
**Fill Handle**. After that, we drag it up to the cell**F15**as**F15**averages the values of**E15**and**E16**.

- The centered moving average values therefore will look like the following image:

**๐ Read More: ****Calculate Displaced Moving Average with Formula in Excel**

## ๐ย Important Notes

๐๏ธย We should be careful when dragging the **Fill Handle** across all the cells. The cells might give values but we should check if the values are considering the average of data in groups of 3 or 4 in case of odd and even time periods.

๐๏ธย The cell from which we started to input the values of moving averages in case of even periods is determined by the addition of intermediate rows and later eliminating them. We should take care in understanding this aspect of placing the average value.

## ๐ย Takeaways from This Article

You have taken the following summed-up inputs from the article:

๐ **ย **Moving averages for an odd number of time periods is easy to place. Therefore, the moving average calculation is straightforward.

๐ย For even time periods, the moving average calculation needs to go a step further to smoothen the value and place the value in the proper position.

๐ย People widely use centered moving averages for prediction and forecasting.

## Conclusion

I hope that this article has provided you with insights about how we can calculate centered moving average values in Excel. If you want to know more about Excel, please visit our site **ExcelDen**. If you have any queries regarding this topic, feel free to let us know down below in the comment section.

## Related Articles

**7 Easy Ways to Calculate Average Rating in Excel****6 Ways to Solve When Average Formula is Not Working in Excel****2 Easy Ways to Compute Average Excluding 0 in Excel****3 Approaches to Calculate Average of Text in Excel****How to Calculate Average Percentage Change in Excel****5 Steps to Estimate Triple Exponential Moving Average in Excel****7 Quick Ways to Calculate Average Percentage Increase in Excel**