2 Examples to Calculate Centered Moving Average in Excel

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.

Contents hide

📁 Download Excel File

Comparison Between Moving Average and Centered Moving Average

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

1. For Odd Number of Quarters

2. For Even Number of Quarters

📄 Important Notes

📝 Takeaways from This Article

Conclusion

📁 Download Excel File

Download the Excel file below.

Calculating Centered Moving Average in Excel.xlsx

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:

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