by In this short tutorial, you will learn how to quickly calculate a simple moving average in Excel, what functions to use to get moving average for the last N days, weeks, months or years, and how to add a moving average trendline to an Excel chart.
In a couple of recent articles, we have taken a close look at calculating average in Excel. If you've been following our blog, you already know how to calculate a normal average and what functions to use to find weighted average. In today's tutorial, we will discuss two basic techniques to calculate moving average in Excel.
What is moving average?
Generally speaking, moving average (also referred to as rolling average, running average or moving mean) can be defined as a series of averages for different subsets of the same data set.
It is frequently used in statistics, seasonally-adjusted economic and weather forecasting to understand underlying trends. In stock trading, moving average is an indicator that shows the average value of a security over a given period of time. In business, it's a common practice to calculate a moving average of sales for the last 3 months to determine the recent trend.
For example, the moving average of three-month temperatures can be calculated by taking the average of temperatures from January to March, then the average of temperatures from February to April, then of March to May, and so on.
There exist different types of moving average such as simple (also known as arithmetic), exponential, variable, triangular, and weighted. In this tutorial, we will be looking into the most commonly used simple moving average.
Calculating simple moving average in Excel
Overall, there are two ways to get a simple moving average in Excel - by using formulas and trendline options. The following examples demonstrate both techniques.
Calculate moving average for a certain time period
A simple moving average can be calculated in no time with the AVERAGE function. Supposing you have a list of average monthly temperatures in column B, and you want to find a moving average for 3 months (as shown in the image above).
Write a usual AVERAGE formula for the first 3 values and input it in the row corresponding to the 3rd value from the top (cell C4 in this example), and then copy the formula down to other cells in the column:
=AVERAGE(B2:B4)
You can fix the column with an absolute reference (like $B2) if you want to, but be sure to use relative row references (without the $ sign) so that the formula adjusts properly for other cells.
Remembering that an average is computed by adding up values and then dividing the sum by the number of values to be averaged, you can verify the result by using the SUM formula:
=SUM(B2:B4)/3
Get moving average for a the last N days / weeks / months/ years in a column
Supposing you have a list of data, e.g. sale figures or stock quotes, and you want to know the average of the last 3 months at any point of time. For this, you need a formula that will recalculate the average as soon as you enter a value for the next month. What Excel function is capable of doing this? The good old AVERAGE in combination with OFFSET and COUNT.
Where N is the number of the last days / weeks / months/ years to include in the average.
Not sure how to use this moving average formula in your Excel worksheets? The following example will make things clearer.
Assuming that the values to average are in column B beginning in row 2, the formula would be as follows:
=AVERAGE(OFFSET(B2,COUNT(B2:B100)-3,0,3,1))
And now, let's try to understand what this Excel moving average formula is actually doing.
- The COUNT function COUNT(B2:B100) counts how many values are already entered in column B. We start counting in B2 because row 1 is the column header.
- The OFFSET function takes cell B2 (the 1st argument) as the starting point, and offsets the count (the value returned by the COUNT function) by moving 3 rows up (-3 in the 2nd argument). As the result, it returns the sum of values in a range consisting of 3 rows (3 in the 4th argument) and 1 column (1 in the last argument), which is the latest 3 months that we want.
- Finally, the returned sum is passed to the AVERAGE function to calculate the moving average.
Tip. If you are working with continuously updatable worksheets where new rows are likely to be added in the future, be sure to supply a sufficient number of rows to the COUNT function to accommodate potential new entries. It's not a problem if you include more rows than actually needed as long as you have the first cell right, the COUNT function will discard all empty rows anyway.
As you probably noticed, the table in this example contains data for only 12 months, and yet the range B2:B100 is supplied to COUNT, just to be on the save side :)
Find moving average for the last N values in a row
If you want to calculate a moving average for the last N days, months, years, etc. in the same row, you can adjust the Offset formula in this way:
Supposing B2 is the first number in the row, and you want to include the last 3 numbers in the average, the formula takes the following shape:
=AVERAGE(OFFSET(B2,0,COUNT(B2:N2)-3,1,3))
Creating an Excel moving average chart
If you have already created a chart for your data, adding a moving average trendline for that chart is a matter of seconds. For this, we are going to use Excel Trendline feature and the detailed steps follow below.
For this example, I've created a 2-D column chart (Insert tab > Charts group) for our sales data:
And now, we want to "visualize" the moving average for 3 months.
- In Excel 2013, select the chart, go to the Design tab > Chart Layouts group, and click Add Chart Element > Trendline > More Trendline Options…
In Excel 2010 and Excel 2007, go to Layout > Trendline > More Trendline Options.
Tip. If you do not need to specify the details such as the moving average interval or names, you can click Design > Add Chart Element > Trendline > Moving Average for the immediate result.
- The Format Trendline pane will open on the right-hand side of your worksheet in Excel 2013, and the corresponding dialog box will pop up in Excel 2010 and 2007.
On the Format Trendline pane, you click the Trendline Options icon, select the Moving Average option and specify the moving average interval in the Period box:
- Close the Trendline pane and you will find the moving average trendline added to your chart:
To refine your chat, you can switch to the Fill & Line or Effects tab on the Format Trendline pane and play with different options such as line type, color, width, etc.
For powerful data analysis, you may want to add a few moving average trendlines with different time intervals to see how the trend evolves. The following screenshot shows the 2-month (green) and 3-month (brick red) moving average trendlines:
Well, that's all about calculating moving average in Excel. The sample worksheet with the moving average formulas and trendline is available for download at the end of this post. I thank you for reading and look forward to seeing you next week!
Practice workbook
Calculating moving average - examples (.xlsx file)
89 comments
I'm trying to create a formula to get the moving average for 3 period, appreciate if you can help pls.
Date Product Price
10/1/2016 A 1.00
10/1/2016 B 5.00
10/1/2016 C 10.00
10/2/2016 A 1.50
10/2/2016 B 6.00
10/2/2016 C 11.00
10/3/2016 A 2.00
10/3/2016 B 15.00
10/3/2016 C 20.00
10/4/2016 A 4.00
10/4/2016 B 20.00
10/4/2016 C 40.00
10/5/2016 A 0.50
10/5/2016 B 3.00
10/5/2016 C 5.00
10/6/2016 A 1.00
10/6/2016 B 5.00
10/6/2016 C 10.00
10/7/2016 A 0.50
10/7/2016 B 4.00
10/7/2016 C 20.00
Your example 3 above (Get moving average for the last N values in a row) worked perfectly for me if the whole row contains numbers. I'm doing this for my golf league where we use a 4 week rolling average. Sometimes the golfers are absent so instead of a score, I will put "ABS" (text) in the cell. I still want the formula to look for the last 4 scores and not count the "ABS" either in the numerator or in the denominator. How do I modify the formula to accomplish this?
Yes, I did notice if cells were empty the calculations were incorrect. In my situation I am tracking over 52 weeks. Even if the last 52 weeks contained data, the calculation was incorrect if any cell prior to the 52 weeks was blank.
sir / mam , i want to calculate ratio of employees on the basis of "INTIME"
so is there any specific formulas .please help!
thank you.
Hii,
I wanted to track facebook likes on my page on daily basis. Like i want to compare today's likes with previous day's average, whether its hike or less.
Hi,
similar to CAGR which calculates compound annual growth rate in values, is there a formula for calculating the same growth between months. viz, Jan to June of the same year
Wow.. Thank you so much Svetlana :)
you made my day !!
Hi .. I really liked the way it has been explained and was able to do in in a minute.. I also liked your website and will follow it regularly..
I was working with a e excel file where i required to make a moving average of last 2 months data which were in different column i.e(D3:G2)
Can you please let me know what modification is required in the formula so that i can make it work to calculate the same..
Rishabh
Hi Rishabh,
Thank you for your feedback. Assuming that you need a moving average for the last 2 numbers in the same row, you can adjust the formula in this way:
=AVERAGE(OFFSET(D3, 0, COUNT(D3:Z3)-2,1,2))
Where D3 is the first number in the row, and 2 is the number of the last N months to be included in the average.
I have 2 rows, 1st row 2015 sales Jan -Dec (per month),
2nd row 2016 sales Jan - Dec (per month). I want to do a running average for 2016 vs 2015 (2016/2015=%). example; 2016 Jan vs 2015 Jan = %, as i add the next months sales i would need 2016 Jan + Feb vs 2015 Jan + Feb = % , and so on. Is this possible?
You can use average (indirect ("b"&rows (b$1$:b1)*3-2&":"&"b"&rows(b $1$:b1)*3))
To calculate average for column b for every 3 sets of numbers
Hi nikhil,
I can help you in this...you have to ise indirect function to achieve this task
Hi,
I have 1000 numbers starting from 1 to 1000. i want average for every 3 numbers. say from 1 to 3 it should be 2. now my next number will be 4 to 6 then 7 to 10 and so on up to 1000 numbers.
If i use average function then it will do average for 1 to 3 then 2 to 4 then 3 to 5. it is not meeting my requirement.
please help me to solve this case.
Hi nikhil,
I can help you in this...you have to use indirect function to achieve this task
You can use average (indirect ("b"&rows (b$1$:b1)*3-2&":"&"b"&rows(b $1$:b1)*3))
To calculate average for column b for every 3 sets of numbers
✌
Hi,
I have some daily data, with column A having dates and column B having values. I want to calculate the average of the values occurring between two specific dates, for instance between 1/1/2016 to 2/15/2016. How would I be able to do that?
Hi,
I am trying to calculate the average of a given project for the last 4 weeks, how can I do this since the cell will not always be next to each other.
Hi,
I am trying to get a moving average for the 7 previous days, the day itself and then the following 6 days (fortnight avg for a week either side of the day).
Can you help?
thanks
Hi, I have 4 weekly figures so can work out the running total, but how do I work out an average weekly total when I'm adding new figures every week which will need to be included in the weekly figure. ie this week I need the average weekly total of 4 figures but next week it will be for 5 figures.
Thanks
I have face a problem. I want to create a sheet in excel, where 1st day(1st column contain daily score, 2nd column contains TTL score,and 3rd column contains average) as same 4th-6th column 2nd day, as same 7th-9th column. please replay me by mail,who are able to help to create it.
Hello, Noman,
Please send us a small sample table with your data in Excel and include the result you need to get to support@ablebits.com. We'll see if we can help.
Trying to calculate moving averages for working capital budget. ie moving averages for inventory, trade debtors and trade creditors.
Any Help?
Hi,
I need your help on this please. I have "column A" with dates and "column B" with values which has both negative and positive values in it. i want to calculate "average" for a particular date from "columb B". Can you please help me on how to calculate average only for the positive values from "column B".
Thank you for your help in advance.
Hello, Yogi,
Please try one of these formulas:
=AVERAGEIF(B:B,"= 42386",A:A) or
=AVERAGEIF(B:B,B3,A:A)
In these formulas please replace 42386 or B3 with the needed parameter.
Hi,
I need your help on this please. I have "column A" with dates and "column B" with values which has both negative and positive values in it. i want to calculate "average" for a particular date from "columb B". Can you please help me on how to calculate.
Thank you for your help in advance.
Hi,
I have read through several different articles looking for an answer to my problem, got lots of great tips but nothing defiant that helps me now.
Could you please help me on this? I’m trying to create a formula that adds the total amount for a particular supplier that is more than 30 days & in another cell a formula that is more than 60 days, etc…
I have the dates of invoices in column A, supplier’s names in column B and Total value in column C.
Any help would be appreciated, have spent days on this & to no avail. Thanks
Hi Kay,
I think you can use the SUMIFS function. For example, to find the total for supplier "John" for orders made more than 30 days ago, you can use the following formula:
=SUMIFS(C2:C100, B2:B100, "john", A2:A100,"<"&TODAY()-30)
For the detailed explanation of SUMIFS' arguments and more formula examples, please see How to use Excel SUMIFS and SUMIF with multiple criteria.