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.

**method**

### 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,

**=SUM(Array)**

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.

**=SUM(D6:D12)/7**

- 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.

**📕 Read More: ****Calculate Displaced Moving Average with Formula in Excel**

**method**

### 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,

**=AVERAGE(Array)**

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.

**=AVERAGE(D6:D12)**

- 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**

**method**

### 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.

**=D6*$H$6+D7*$H$7+D8*$H$8+D9*$H$9+D10*$H$10+D11*$H$11+D12*$H$12**

- 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.

**📕 Read More: ****2 Examples to Calculate Centered Moving Average in Excel**

**method**

### 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.

**=(D7-E6)*(2/8)+E6**

- 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.

**📕 Read More: ****5 Steps to Estimate Triple Exponential Moving Average in Excel**

**method**

### 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.

__Code__

```
Sub Calculate_7Day_Moving_Average()
Range("E12").Select
ActiveCell.FormulaR1C1 = "=average(R[-6]C[-1]:R[0]C[-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]:R[0]C[-1])”** to calculate the average using a formula containing the **AVERAGE** function of Excel in cell **E12**. Here,** (R[-6]C[-1]:R[0]C[-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.

**📕 Read More: ****6 Ways to Solve When Average Formula is Not Working in Excel**

## 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.

**=COUNT(array)**

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.

**=AVERAGE(OFFSET(D5,COUNT(D6:D17,G6:G17)-12,0,12,1))**

**🔨 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.

## Conclusion

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**.

## Related Articles

**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**