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
- What is a Moving Average in Excel?
- Moving Average() Excel Formula
- What are the types of moving average in Excel?
- How to Use Moving Average Function in Excel?
- Important Things to Remember
- Frequently Asked Questions (FAQs)
- Download Template
- Recommended Articles
- 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:
- number1: (mandatory) The number or range for which average is required
- number2: (Optional) Any other numbers or cell ranges for which an average is required. You can give numbers up to 255.
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 Formulas tab, and in the Function Library, click on More Functions.
- Here, click on Statistical and select the AVERAGE function.
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 OK and enter the details required to calculate the moving average.
These three methods are effective in calculating the moving average for different datasets.
Let us look at various examples of calculating the moving average.
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 Data Analysis option in the Analysis group.
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.
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 Insert tab, and choose the required chart. We have chosen the 2D line chart here.
- Next, click on the + sign on the top right of the chart and select the Trendline option.
- Click on the arrow in the Trendline option and choose the Two period Moving Average option. You get two trendlines.
- To convert them to moving-average lines, go to the Format tab and select the trendline you wish to format. Click on the Format Selection option.
- Now, on the right side, in the Format Trendline option, you can choose the time interval, a custom name, and the 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.
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
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)
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.
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.
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)
The moving average in Excel can be plotted when we use the in-built Analysis Tool by selecting the Chart Output checkbox in the pop-up window. Also, we can plot it by selecting the appropriate chart from the Charts group under the Insert tab.
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.
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 –