How to calculate 7 day moving average in Excel

How to calculate 7 day moving average in Excel

Moving Average is otherwise called running average or rolling average. There is something common about the three different names – moving, running, and rolling.

Yes, this type of average has to do with movement. But do we go about it? There is no much difference between the conventional Average and the moving average. I hope you know how to calculate the conventional Average. Assuming, we collate the data of daily temperature for three days. We can easily calculate the Average. When finding the moving Average, the time frame remains the same, but the values will change as new data is added to the list.

Now, let's consider an example; we can calculate the average value of the temperature of three days by considering the data for Day 1, Day 2, and Day 3, and we use the number of days as 3.

On the fourth day, you are asked again to calculate the Average. You so do by considering data of Day 2, Day 3, and Day 4, ignoring the oldest data. But we still use three as the number of days in our calculation. I hope you can see that in moving Average, the number of days is kept constant, but we use the latest data in the calculation as new data keeps rolling in.

Moving Average is widely used in the bank for analyzing technical data and stock market data.

Let us work on the dataset below and see how we can calculate the seven days moving average.

 

 

 

 

 

 

 

 

 

1. We can quickly do this by using the average function on excel.

=AVERAGE (cell1:cell2)

2. If we want to find the moving average for 3 days, you have to scroll to cell C4 and enter the formula:

=AVERAGE(B2: B4)

3. Hit enter and your result for the simple moving average for each day will show up in the C columns.

 

 

 

 

 

 

 

 

 

4. You can see the result in the image above. You can select and drag it to the number of days you want to calculate.

5. To calculate the moving Average for seven days, simply move your cursor to C8 and enter the Average formula below:

=AVERAGE(B2: B8)

6. Press enter and your result for 7 days will be displayed.

You can also find the moving Average by using the data analysis tab on the excel window. But you need to activate the data analysis tool pack on your excel. However, the Average function, which we explained in this tutorial, is a quicker option for calculating simple moving averages.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.