From Operational Management to Demand Planning, Stock market to Small businesses everywhere one thing in common, is the Moving Average Calculation. Predominantly moving average calculation is the average in a certain period. MS Excel is the most widely used software that bolsters the calculation of the Moving Average of certain times with various approaches using different functions. Calculation of the moving average based on the past week is widely applied. In this article, we are going to illustrate several approaches to calculate the 7-day moving average in Excel.
📁 Download Excel File
To practice please download the Excel file from here:
What is Moving Average?
Moving Average is a statistical method that is used for long-term forecasting. Moving Average known as MA is a technique that calculates the recent overall trend in a dataset after a certain period interval. In Operations and Sales management, the dataset is simply the sales volume from the historical data of the company. This technique is useful for short-term as well as long-term trends. By calculating the moving average one can predict short-term fluctuations over a specified time. However, Moving Average is the average data for a certain period. Suppose sales data of 5 days are, 10 units, 08 units, 16 units, 20 units, and 15 units. If we measure the 3-day Moving Average,
Learn to Calculate 7-Day Moving Average in Excel with These 5 approaches
In this article, we will explore 5 handy approaches to calculate the 7-day moving average in Excel. Following that we considered a data set named Monthly Sales Data of Amigo Super Store containing Day, Date, and Sales. The dataset also has 4 columns as well as 17 rows. To construct methods, we are going to expound on the use of the SUM, AVERAGE, OFFSET functions, and VBA Macro. Calculation of Simple Moving Average, Weighted Moving Average as well as Exponential Moving Average is also illustrated concisely. Let’s get started.
1. Calculate Simple Moving Average Forecasting of 7 Day Using Formula in Excel
Considering the values of the last 7 days and counting the summation and dividing by the period, one can easily calculate simple moving average forecasting of 7 days. To sum up, values automatically, consider the SUM function. The general syntax of the SUM function is as follows,
Please follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell E12 under the 7-Day Moving Average heading.
- Secondly, write down the following formula in the E12 cell containing the SUM function.
- Finally, summing up the sales data and dividing by 7, we obtain the result of $2,828 from the 7th day in the E12 cell. Further, use the Fill Handle to auto-fill the cells till the E17 cell.
2. Calculate 7 Day Rolling Average Based on Date Using AVERAGE Function in Excel
The moving Average is the average of a certain period. Excel has a function named AVERAGE which calculates the average sales for the last 7 days. It is also known as 7-day Moving Average. The general syntax of the AVERAGE function is as follows,
Please follow the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, select cell E12 under the 7-Day Moving Average heading.
- Then, write down the following formula in the E12 cell containing the AVERAGE function.
- Finally, calculating the average of the last 7 days, we obtain the result of $2,828 from the 7th day in the E12 cell. In addition, pull down the Fill Handle to auto-fill the cells till the E17 cell is.
📕 Read More: 2 Easy Ways to Compute Average Excluding 0 in Excel
3. Find 7 Day Weighted Moving Average Formula in Excel
The Weighted Moving Average is the moving average of a certain period with varying degrees of importance in a dataset. Suppose weight varies in 10%, 10%, 15%, 15%, 15%, 15%, and 20% throughout a week. Therefore the calculation is as follows,
Please Check out the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, navigate a cell i.e. E12.
- Secondly, insert the following formula in the E12 cell.
- Next, lock the weighted cells that are located below the Weight heading in column H to restrict the reference cells during the application of auto-filling.
- Therefore, get the moving average from the 7th day varying the importance of days.
4. Compute Exponential Moving Average Forecasting in Excel
Exponential Moving Average is a technical statistical method that shows the change of value after a certain period or the forecasted value. However Exponential Moving Average is quite different from the Simple Moving Average. In the meantime, it results in nearly the most accurate data. Please check out the formula to calculate Exponential Moving Average as follows.
Please follow the necessary steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, there is no value in the E7 cell. Therefore, insert the value copying from the D6 cell.
- Secondly, select the E7 cell and type the following formula.
- Therefore, we achieve the result of $2,978 in the E7 cell under the 7-Day Moving Average heading.
- Finally, auto-fill the remaining cells using the Fill Handle tool.
5. Use VBA Macro to 7-Day and 10-Day Compute Moving Average from Formula in Excel
Generating a VBA code, one can calculate a 7-Day or 10-Day moving average from the dataset within a second. By getting the Developer option in the Top Ribbon, you can generate a program on your own. Please follow the necessary steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Check first if your Developer option is available or not.
- Secondly, to enable the Developer option, Right-Click on the mouse at the Top Ribbon and select Customize the Ribbon.
- Thirdly, click on the Developer option and hit the OK button.
- Now Developer mode is on.
- Then, click on the Visual Basic feature.
- Next, insert the following VBA code in the dedicated worksheet VBA and hit on the Run icon.
Sub Calculate_7Day_Moving_Average() Range("E12").Select ActiveCell.FormulaR1C1 = "=average(R[-6]C[-1]:RC[-1])" Selection.AutoFill Destination:=Range("E12:E17") End Sub
🔨 Code Breakdown
👉 Firstly, select the E12 cell first writing Range(“E12”).Select.
👉 Then, insert ActiveCell.FormulaR1C1 = “=average(R[-6]C[-1]:RC[-1])” to calculate the average using a formula containing the AVERAGE function of Excel in cell E12. Here, (R[-6]C[-1]:RC[-1]) means the D6:D12 range.
👉 Finally, Selection.AutoFill Destination:=Range(“E12:E17”) to autofill the remaining cells ranging from E12 to E17.
- Therefore, by executing the VBA code, we obtain the 7-Day Moving Average from the 12th row to the 17th row automatically.
Find Moving Average for Last 12 Months Using OFFSET Function in Excel
Suppose, one asks you to measure the moving average of the Last N months. In contrast, data from the past two months also missing. Don’t worry. Using the OFFSET function as well as the COUNT function, you will be able to calculate the moving average for the last 12 months. The OFFSET function is used to consider reference arguments. The general syntax of the OFFSET function is as follows.
=OFFSET(reference, rows, cols, [height], [width])
On the other hand, the COUNT function counts the cells from a dataset. The syntax of the COUNT function is as follows.
Please check out the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, select a cell i.e. G19 to get the Average for the Last 12 Months.
- Secondly, insert the following formula in the G19 cell.
🔨 Formula Breakdown
👉 Firstly, the COUNT function counts cells from D6:D17 and G6:G17 ranges.
👉 Then, deduct the 12 from the counting of the cells in the range, and the formula determines to start counting after the 10th cell.
👉 Next, the D6 cell is the reference cell of the dataset and the OFFSET function counts the last 12 data under the Sales heading.
👉 Finally, the AVERAGE function calculates the average based on the last 12 data.
📄 Important Notes
🖊️ Enable the Developer menu first to execute VBA code.
🖊️ The total weighted sum should be 100% while calculating the weighted moving average.
🖊️ Use cell reference carefully following the Simple Moving Average, Exponential Moving Average, and Weighted Moving Average formulas.
📝 Takeaways from This Article
📌 SUM function to compute for 7-day Moving Average.
📌 AVERAGE function to calculate the Moving Average for any specified days.
📌 Exponential Moving Average formula for forecasting.
📌 Weighted Moving Average calculation based on the importance of the days.
📌 VBA Macro to calculate 7-Day Moving Average automatically.
📌 OFFSET function to get the Moving Average for the last 12 months.
In this article, we expound on 5 catchy approaches to calculating the 7-day moving average in Excel. I hope you enjoyed your learning and will be able to calculate moving averages. Any suggestions, as well as queries, are appreciated. Don’t hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.
- 7 Easy Ways to Calculate Average Rating in Excel
- 3 Approaches to Calculate 5 Star Rating Average in Excel
- 7 Ways to Calculate Average Attendance with Formula in Excel
- 6 Quick Ways to Calculate Average Percentage of Marks in Excel