SubscribeCalculate Moving Average
Moving average is frequently used to understand underlying trends and helps in forecasting. MACD or moving average convergence / divergence is probably the most used technical analysis tools in stock trading. It is fairly common in several businesses to use moving average of 3 month sales to understand how the trend is.
Today we will learn how you can calculate moving average and how average of latest 3 months can be calculated using excel formulas.
Calculate Moving Average
To calculate moving average, all you need is the good old AVERAGE excel function.
Assuming your data is in the range B1:B12,
- Just enter this formula in the cell D3
- =AVERAGE(B1:B3)
- And now copy the formula from D3 to the range D4 to D12 (remember, since you are calculating moving average of 3 months, you will only get 10 values; 12-3+1)
- That is all you need to calculate moving average.
Calculate Moving Average of Latest 3 Months Alone
Lets say you need to calculate the average of last 3 months at any point of time. That means when you enter the value for the next month, the average should be automatically adjusted.
We can do that using excel formulas AVERAGE, COUNT and OFFSET
First let us take a look at the formula and then we will understand how it works.
=AVERAGE(OFFSET(B4,COUNT(B4:B33)-3,0,3,1))
So what the heck the above formula is doing anyway?
- It is counting how many months are already entered – COUNT(B4:B33)
- Then it is offsetting count minus 3 cells from B4 and fetching 3 cells from there – OFFSET(B4,COUNT(B4:B33)-3,0,3,1). These are nothing but the latest 3 months.
- Finally it is passing this range to AVERAGE function to calculate the moving average of latest 3 months.
Your Home Work
Now that you have learned how to calculate moving average using Excel, here is your home work.
- Lets say you want the number of months used to calculate moving average to be configurable in the cell E1. ie when E1 is changed from 3 to 6, the moving average table should calculate moving average for 6 months at a time. How do you write the formulas then?
Don’t look at the comments, go and figure this out for yourself. If you cant find the answer, come back here and read the comments. Go!
This post is part of our Spreadcheats series, a 30 day online excel training program for office goers and spreadsheet users. Join today.
Trackbacks & Pingbacks
- Pingback by Array Formulas and Average Calculations « Excel News on April 30, 2009 @ 7:53 pm
- Pingback by Become an Excel Guru in 31 Days | Pointy Haired Dilbert: Learn Excel Online - Chandoo.org on March 11, 2010 @ 9:53 am
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Hello, just recently found your website and I’m loving all the tips. Thank you for all your tutorials. It’s exactly I needed however, I ran into a bit a problem as I am also using Vlookup with Offset. For instance, in your example, I would use Vlookup in my template so that as I put in new data every month, it would automatically update the sales data each month.
=IF(ISBLANK(VLOOKUP($A15,Data,45,FALSE)),” “,(VLOOKUP($A15,Data,45,FALSE))).
My problem is in my OFFSET formula, I have COUNTA which obviously counts any cells with formulas, even ” “. Any ideas how to incorporate these two functions better, especially when I am trying to graph and average that last 12 months?
I would appreciate any ideas you or your readers my have. Thanks, again, for the the awesome site!
=AVERAGE(OFFSET(B1,(COUNT(B:B)-E1),0,E1,1))
Good work out for my foggy brains.
not a perfect answer! only conceptually correct.
=IF(COUNT($A$5:A7)>=$B$2,AVERAGE(OFFSET($A$5,COUNT($A$5:A7)-$B$2,0,$B$2,1)), “”)
where
B2 contains my Moving Average number
my data lies in cells a5 through a22
Chandoo, my first post ever. Nice job with your site though. totally awesome
@Twee… welcome to PHD and thanks for asking a question. I am not sure if I understood it correctly though. Have you tried using count instead of counta?
You havent shown us the offset formula, without looking that fixing it would be difficult…
Hi Chandoo – coincidentally I did a post on moving averages as well recently:
http://newtonexcelbach.wordpress.com/2009/04/25/moving-averages-and-user-defined-array-functions/
You’ll have to wait for the next post to find out why I used a UDF, rather than the built-in average function
Hi Chandoo,
Thank you for suggesting I use Count instead of Counta. I don’t know I was so stuck on counta and didn’t think of count instead!! I just tried it and it worked beautifully!! Oy!! Something so simple and I was stuck on it! Thanks, again, for the tutorial on OFFSET because it’s indeed powerful!
@Paresh, IndianJewel: Thanks for sharing 2 very good ways to do this.
Learning to offset is very critical when you are working with ranges of varied (and unknown) sizes.
@Doug: I have seen your post in google reader after scheduling this, otherwise I would have linked to it from this post itself… Thanks for sharing the URL.
@Twee… you are welcome
I am happy you could solve this…
Hey guys,
Can someone help me to figure out how to calculate the average based on a 365-day rolling average.
I do have up to 6 month of daily data and keep collecting them and would like to know what would be the 365-rolling average.
Assume you data is in B2 to B366 then using
=SUM(B2:B366)/COUNT(B2:B366)
Is this a right way to do it. Or use this formula
=SUM(B2:B366)
then =B2/365
I don’t get the same results and need to know which one is right and why?
Can someone out there help me?
Thanks,
ForMcmurray
Sorry I had to fix my formula…
Hey guys,
Can someone help me to figure out how to calculate the average based on a 365-day rolling average.
I do have up to 6 month of daily data and keep collecting them and would like to know what would be the 365-rolling average.
Assume you data is in B2 to B366 then using
=SUM(B2:B366)/COUNT(B2:B366)
Is this a right way to do it. Or use this formula
=SUM(B2:B366) result goes to C2
then =C2/365
I don’t get the same results and need to know which one is right and why?
Can someone out there help me?
Thanks,
ForMcmurray
@FortMcmurray: Welcome to PHD and thanks for asking a question. The reason why you are seeing different results is because your data might have zero / blank elements. When calculating averages often you may want to omit zero / blank values. Also, instead of using sum()/count(), you may want to try the average() formula. You can learn more about average formula here: http://chandoo.org/excel-formulas/average.html
As long as you have values in all the cells, all sum()/count(), sum()/365, average() should return the same value.
Thanks Chandoo for your help. But how do you address situation where you do not have a whole year data but nonetheless you are trying to calculate the moving average based on a 365-day?
Do you put zero for those cell that you don’t have value or leave them blank?
@FortMcmurray: The point of moving average is, it is calculated for a moving window of values. So when you have values less than a year, you cannot calculate moving average. Whenever we calculate moving average of n values over a range of M values (n<M), you get M-n+1 moving averages.
Hi,
I am trying to calculate 30 days rolling data . Can any one help me out here. I have 30 days figure. and going from 1st of the next month I want my data to be automatically picked up on 30 days rolling period. Any one help here will be highly appreciated.
@Amber, you can calculate this very easily. Assuming you have data in the range A1:A1000, in B30, you can write the formula average(a1:a30) and then copy the formula down.
hello..,
can you help me to calculate the sales of Jan 09,Feb 09,Mar 09 ..ect.using this formula..in the above example ?
@Shamsuddin.. you mean calculating sales values from the moving average?
HELP!! i am new at excel and i need to find the average YTD over 12 months. each month has its own month total on an individual worksheet. what would the formula be?
@Cordell.. is the value for month-wise total in the same cell in individual sheets? If so, you can use 3d References to solve this… like,
=AVERAGE(JAN09:DEC09!A1)
(assuming sheet names are JAN09, FEB09… DEC09 in that order)
You are THE MAN
thank you for this website
God Bless
have a question here:
i have measurements performed every 10 minutes for a longer period (few months)
i want to calculate daily averages without having to select all data the needs to go into these averages
can anyone help
thanks
A.
@Alejandro… assuming you have measurement date-time in column A and measurement in column B,
You can get daily averages like this:
List down the dates for which you need daily averages in column C.
In column D, write a formula like this: =sumproduct(–(int($a$1:$a$100)=c1),($b$1:$b$100))/sumproduct(–(int($a$1:$a$100)=c1))
change the range values as you please. For more on sumproduct formula visit – http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/
Chandoo,
I have a large database with several columns of data over 3 years. I am trying to build a formula at the end that will take rolling average of the last 30 pieces of data. I understand your formula all the way until you get to the end what is the 0,3,1 mean? Does this stay the same for all applications?
=AVERAGE(OFFSET(B4,COUNT(B4:B33)-3,0,3,1))
Chandoo,
I have several columns that have information in them. I am trying to create a last thirty pieces of data moving average. Each time I add data I want it automatically update the moving average. The problem I have is that I have some columns that have sporatic data in them and when I apply the above formula It is not taking the last 30 pieces of data. If I have 3 blank cells in the column it is taking the average of the last 33 pieces of data. Is there a way to only have it look at cells with data in them and count the last entered data backward 30 to calculate the moving average? Below is the formula I have entered. Any thoughts? (G4 is the start of the data in the column) (G4:G314 is the range of data)
=AVERAGE(OFFSET(G4,COUNT(G4:G314)-30,0,30,1))
@Geoff… sorry for responding late. I forgot your comment until today.
For OFFSET help pls visit http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/ and see the examples. You should be able to make sense of them.
Coming to your problem regarding blanks and sporadic data, here is how I would solve it.
Insert a new column next to column G where you have the data. Write “1″ in first cell. In the subsequent cells write =IF(G5=”",H4,H4+1)
This will show numbers in incremental order but shows same numbers when the value in column G is a blank. Paste this formula in the range H5:H314.
Now, the moving average formula becomes =AVERAGE(OFFSET(G4,MATCH(MAX(H4:H314)-30+1,G4:G314,0),0,
MATCH(MAX(H4:H314),G4:G314,0)-MATCH(MAX(H4:H314)-30+1,G4:G314,0)+1
,1))
Essentially we have introduced a new column and are using MATCH to find the position of 30 th cell from last with a value in the range G4:G314. Once we know the position of it (MATCH(MAX(H4:H314)-30+1,G4:G314,0)) , we just offset the range from that point MATCH(MAX(H4:H314),G4:G314,0)-MATCH(MAX(H4:H314)-30+1,G4:G314,0)+1 cells.
I am going to write another tutorial on this problem as it is quite interesting. You can get a downloadble file then. Meanwhile you can try these formulas. You seem like you already know what to do, so this should help you get going.
I realize the formulas are kind of messed up. This is how it should have read.
—
Now, the moving average formula becomes =AVERAGE(OFFSET(G4,MATCH(MAX(H4:H314)-30+1,G4:G314,0),0,
MATCH(MAX(H4:H314),G4:G314,0)
-MATCH(MAX(H4:H314)-30+1,G4:G314,0)+1
,1))
—