**What is a Moving Average in Excel?**

A moving average in Excel is defined as the averages that are calculated using different subsets from an entire dataset. It is also called a rolling mean or a moving mean. Here, the time remains the same, while the average is moving because new data keeps adding to the dataset.

For instance, let us calculate the sales’ simple moving average (SMA) over one month. First, write the average function for a set of three values beginning from the first three and calculate accordingly. Now, enter the function **=AVERAGE($B2:$B4)** in **cell C4**, where we can get the average of the first three months. Thus, you get the moving average of every three months for a year.

## Table of contents

###### Key Takeaways

- The moving average in Excel is a series of averages calculated from data points of different subsets in a complete data set.
- It can be calculated simply using the AVERAGE() function in Excel. We can also use the Data Analysis in-built tool of Excel to calculate the moving average.
- You can plot graphs to check the smoothening of the fluctuations in the data and easily recognize trends.
- The OFFSET function with AVERAGE helps you calculate the moving average for varying periods.

**Moving Average() Excel Formula**

The simplest formula for finding the moving average in Excel is through the **AVERAGE** Excel function. The syntax of the function is as follows:

(mandatory) The number or range for which average is required*number1:*(Optional) Any other numbers or cell ranges for which an average is required. You can give numbers up to 255.*number2:*

The moving average for a variable time of N weeks, months, or years can be calculated using a combination of OFFSET with AVERAGE.

### What are the types of moving average in Excel?

The main types of Moving Average in Excel are:

#### #1 – **Simple moving average in Excel (SMA)**

It is the average of a subset of data at given intervals. For instance, if you have the temperature of a city for ten days, if you calculate the sum and divide it by 10, you get the 10-day moving average.

#### #2 – Weighted moving average in Excel (WMA)

Here, there is more emphasis on recent data compared to the weightage of past data.

#### #3 – **Exponential moving average (EMA)**

It emphasizes the most recent data points as compared to past ones and reacts more significantly to recent changes.

**How to Use Moving Average Function in Excel?**

The moving average is calculated based on the time interval. Usually, it is estimated simply in three ways:

- Calculate the moving average in Excel manually entering the AVERAGE() function
- Entering AVERAGE through the Excel ribbon
- Accessing Moving Average through the Excel ribbon Data tab

**Manually entering the AVERAGE() function**

Let us look at how we can find the simple moving average for the sales data of an air-conditioning company for one fortnight. For this, you may use the simple AVERAGE formula.

**Step 1:** We have the data on the number of air-conditioners sold for 15 days. But first, we must find the 5-day average. Then, to use the AVERAGE function, you must specify the cell references range as a relative reference, especially the rows.

So, you may either use **=AVERAGE(B2:B7)** or **=AVERAGE($B2:$B7) **in cell **C7.**

**Step 2: **Now, press Enter. You get the first five values’ average from **B2 to B7.** As you copy the formulas down the column, the value of the row changes and includes new values for the 5-day average.

We enter the formula in **C7** because, if it is started in the first cell **C2**, there won’t be sufficient data for calculating the 5-day average.

Thus, you can enter the formula and calculate the 5-day average to understand sales trends.

**Entering AVERAGE through the Excel Ribbon**

To find the moving average, place the cursor where you want to see the result and enter the **AVERAGE() **function through the Excel ribbon.

- Go to the
tab, and in the*Formulas**Function Library*, click on*More Functions.* - Here, click on
and select the*Statistical*function.*AVERAGE*

**Accessing through the Excel Ribbon Data Tab**

You can also access the moving average in Excel through the ** Data** tab in the Excel ribbon.

**Step 1: **Go to the Data tab and click on Data Analysis in the Analysis group.

**Step 2:** In the pop-up dialog window, click on ** Data Analysis**, and you can scroll and select

**Moving Average**from the list. Now, click

**and enter the details required to calculate the moving average.**

*OK*These three methods are effective in calculating the moving average for different datasets.

**Examples**

Let us look at various examples of calculating the moving average.

**Example #1**

Here’s a simple example of how to apply the ** Data Analysis** option to obtain the moving average.

Below are the weekly sales details of a salesman selling a product. But first, we must calculate the moving average for weight 2.

**Step 1: **Go to the ** Data** tab and select the

**option in the**

*Data Analysis***group.**

*Analysis***Step 2:** You get a data analysis pop-up window. Select the option **“Moving Average.”**

**Step 3: **Now, you get a pop-up window.

- Enter the input range, which is the range of the data set. Here it is from B2 to B9.
- Enter the interval, 2, in this case.
- Enter the output range where you want the output, C2:C9. Press OK.

**Step 4:** Here, we have checked the ** Chart Output** check box. Hence, we get the output with a chart for the moving average. Since the first cell C2, did not have enough data to find the moving average, we get #N/A.

**Example #2**

Let us calculate the moving average for a city’s average temperature over a period of 12 months. Here, we are doing it for 3- and 6-month intervals. Also, plot a graph of the values.** **

**Step 1:** We must apply the formula below to calculate the three-month average.

**Step 2: **Go to cell C4 and type =AVERAGE(. Select the cells from B2 to B4 since we need the moving average of 3 months.

- Hence, you get the formula
**=AVERAGE(B2:B4)**in cell C4. - Press Enter. Copy the formula to all cells from C4 to C13. You get the three-month moving average of the temperature.

- If we add the formula
**=AVERAGE(B1:B3)**in cell C2, you do not get an error. - It is because the AVERAGE() function tends to ignore text values and empty cells and finds the average of the numbers present in the range.
- Hence, you get a moving average of 12, as 12 is the only number in this range.

**Step 3: **To find the six-month moving average, add the formula **=AVERAGE(B3:B8)** in cell D8.

**Step 4: **Now, drag the Autofill handle up to D14. Here, you get the six-month moving average for the different data points.

**Step 5:** Now, let us plot the graph for these two moving averages to smooth out any fluctuations.

- Select the range B3 to B14, go to the
tab, and choose the required chart. We have chosen the 2D line chart here.*Insert* - Next, click on the + sign on the top right of the chart and select the
option.*Trendline*

- Click on the arrow in the
option and choose the*Trendline*option. You get two trendlines.*Two period Moving Average* - To convert them to moving-average lines, go to the
tab and select the trendline you wish to format. Click on the*Format*option.*Format Selection*

- Now, on the right side, in the
option, you can choose the time interval, a custom name, and the*Format Trendline**Moving Average*option.

- Repeat the same for the 6-month interval trendline as well.

Thus, we obtain a moving average in Excel chart for our data. It shows the smoothening of the fluctuations in the original data.

**Example #3**

We have so far calculated the simple moving averages for a dataset through different methods. Now, let us calculate the moving average when the number of periods is variable.

**Finding the moving average for the last n values of a column**

To find the moving average of the bottom n values, you must use the OFFSET function in combination with the COUNT and AVERAGE functions.

**Step 1:** Now, let us consider the OFFSET function. It has five arguments.

- The first argument specifies the starting point of the range for which we want to calculate the average. So, it is
**B2**in this case. - The following argument specifies how many columns you want to move down. In this case, let us consider the last four months’ average. Hence, it should go back four times from the bottommost cell.
- The COUNT function will identify the last cell in the column, and then you can subtract four from it. So, the second argument will be
**COUNT(B4:B100) – 4.**We choose a considerable number of 100 as the range to make the data set expandable. - Next, we wish to remain in the same column. So, argument 3is
**0**. If you want to move one column to the right, you give 1. One column to the left means -1. - The last two arguments specify the height and width. So, 1,1 means a single cell. 2,1 means two rows and one column. Here, we need the last four rows; hence we specify it as 4,1. Next, we must find the average of this selected range. Therefore, type the following formula in cell E4.

**=AVERAGE(OFFSET(B2,COUNTA(B2:B100) – 4,0,4,1))**

**Step 2:** Press Enter. Thus, you get the moving average of the last four months.

**Step 3:** Now, you can dynamically change the values for the last few months of any count.

### Important Things to Remember

- The moving average eases out fluctuations in the data and identifies areas of support and resistance.
- Here, we can find it using the simple statistical function AVERAGE in Excel.
- Microsoft Excel has a built-in Data Analysis ToolPak to calculate simple moving averages.
- It is used mainly in fields such as weather forecasting with temperatures to understand the trends and by financial analysts to get the average value of a security over time.

**Frequently Asked Questions (FAQs)**

**1. Why is moving average in Excel not working?**

When the range of cells provided does not contain enough data points in the form of numbers, you get #N/A when you use the Data Analysis tool in Excel for the moving average.

**2. What is the use of moving average in Excel?**

The moving average is used to find the average of subsets in a data set which helps understand trends in weather forecasting and financial analysis of stock prices. In addition, it helps smooth out fluctuations in the curve due to the data points.

**3. How to create a 7-day moving average in Excel?**

The seven-day moving average can be calculated using the AVERAGE function by specifying the range of cells to contain 7 days. For example, =AVERAGE(B2:B8)

**4. How to plot moving average in Excel?**

The moving average in Excel can be plotted when we use the in-built ** Analysis Tool** by selecting the

**checkbox in the pop-up window. Also, we can plot it by selecting the appropriate chart from the**

*Chart Output***group under the**

*Charts***tab.**

*Insert*### Download Template

This article must help understand the **Moving Average in Excel**, with its formula and examples. We can download the template here to use it instantly.

### Recommended Articles

Guide to Moving Average in Excel. Here we explain how to use moving average, its formula, examples & downloadable excel template. You can learn more from the following articles –

## Leave a Reply