The moving average is also known as the running average or rolling average. It’s pretty much the same as the normal moving average except its input data keeps updating. In this article, you will learn to calculate the 7-day moving average in Excel.
What is the Moving Average?
The moving average is a type of average of numbers where the time frame remains the same but the data keeps updating as new data is added.
For example, we have a list of the daily arriving customer numbers of a shop. To get the average customer number, we generally sum up the total number of customers for 7 days and then divide the sum by 7. This is the general average calculation concept.
In the case of moving average or running average, the days continue. So the number of customers keeps updating. As a result, the moving average changes too. It’s not a static value now.
Types of the Moving Average
The moving average can be divided into 3 major types. Those are,
- Simple Moving Average
- Weighted Moving Average
- Exponential Moving Average
Simple Moving Average: When you calculate the average data of a certain numerical value by summing them up first and then dividing, it’s called Simple Moving Average. You can calculate the Simple Moving Average in Excel using the AVERAGE function, or the SUM function.
Weighted Moving Average: Suppose, you want to forecast the average temperature. It is possible that the latest data can predict better than the old data. In that case, we put more weight on the recent data. Thus, calculating the moving average with weights is called the Weighted Moving Average.
Exponential Moving Average: The Exponential Moving Average is a type of moving average where more weights are given to the recent data and fewer weights for the older data.
How to Calculate 7 Day Moving Average in Excel: 4 Ways
1. Use the AVERAGE Function to Calculate the 7 Day Simple Moving Average in Excel
The easiest way to calculate the moving average in Excel is to use the AVERAGE function.
All you need to do is,
❶ Insert the AVERAGE function in a cell first, where you won’t return the moving average. In the argument second of the AVERAGE function, insert the cell range that contains the data of 7 days like the formula below:
=AVERAGE(C5:C11)❷ Then press the ENTER button.
Read More: How to Calculate Weekly Average in Excel
2. Calculate the 7 Day Simple Moving Average in Excel Using the SUM Function
The alternative way to calculate the simple moving average is to use the SUM function.
To use the function,
❶ Select a cell first, where you want to return the moving average. After that, enter a cell range of 7-day data in the argument section of the SUM function like the following formula:
=SUM(C5:C11)/7❷ After that press the ENTER button to execute the formula.
Read More: How to Calculate Sum & Average with Excel Formula
3. Find the 7 Day Weighted Moving Average in Excel
If you know the actual weights of the data, you can easily calculate the Weighted Moving Average. For example, we have the following weights for the 7-day moving average formula: 0.2, 0.1, 0.1, 0.2, 0.3, 0.05,0.05.
To calculate the weighted moving average, follow the steps below:
❶ Enter the following formula of Weighted Moving Average in cell E5.
=0.2*C5+0.1*C6+0.1*C7+0.2*C8 +0.3*C9+0.05*C10+0.05*C11❷ Now press the ENTER button to execute it.
Read More: Calculate Moving Average for Dynamic Range in Excel
4. Calculate the 7 Day Exponential Moving Average in Excel
The general formula to calculate the 7-day Exponential Moving Average (EMA) in Excel is,
EMA = [Recent Value - Last EMA] * (2 / N+1) + Last EMAIn the formula above, you can insert any value for the N as per your recruitment. As we are calculating the 7-day EMA, N = 7.
As for this particular example, we don’t have any last EMA value hence,
❶ Enter the following formula in cell E5 to copy the first value of the data.
=C5
❷ Then type the following formula in cell E6 and the rest of the cells.
=(C6-E5)*(2/8)+E5❸ Finally press the ENTER button to execute the above formula.
Read More: How to Determine Triple Exponential Moving Average in Excel
Insert a Moving Average Chart in Excel
To insert a moving average chart in Excel,
❶ Select the moving average values first.
❷ Then go to the Insert tab.
❸ After that insert a Clustered Column 2-D chart.
❹ Then click on the 2-D chart and go to the Chart Design tab.
❺ Navigate to the Add Chart Element.
❻ From the drop-down menu, choose Trendline.
❼ under the Trendline, you will find the Moving Average. Just click on it to apply.
After going through all the above steps, you will get a moving average chart like the following one:
Read More: How to Generate Moving Average in Excel Chart
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed how to calculate the 7-day moving average in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.
