Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate 7 Day Moving Average in Excel (4 Ways)

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 7-day moving average in Excel.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


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.

7 Day Moving Average chart


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


4 Ways to Calculate the 7 Day Moving Average in Excel

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.

Use the AVERAGE Function to Calculate the 7 Day Simple Moving Average in Excel

Read More: How to Calculate Average in Excel (Including All Criteria)


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.

Calculate the 7 Day Simple Moving Average in Excel Using the SUM Function

Read More: Average Attendance Formula in Excel (5 Ways)


Similar Readings


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.

Find the 7 Day Weighted Moving Average in Excel

Read More: [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions) 


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 EMA

In the formula above, you can insert any value for the N as per your recruitment. As we are calculating the 7-day EMA, thus 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.

Calculate the 7 Day Exponential Moving Average in Excel

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.

Insert a Moving Average Chart in Excel

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 (4 Methods)


Conclusion

To sum up, we have discussed how to calculate 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. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo