# How to Calculate Centered Moving Average in Excel (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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 interval 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. ### 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 MAmoving 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.  Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  