Calculate Moving Average

Posted on April 28th, 2009 in Learn Excel - 173 comments

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

Rolling Average of Last 3 Months
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.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

173 Responses to “Calculate Moving Average”

  1. Twee says:

    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!

    • Chandoo says:

      @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…

  2. paresh says:

    =AVERAGE(OFFSET(B1,(COUNT(B:B)-E1),0,E1,1))

    Good work out for my foggy brains.

  3. paresh says:

    not a perfect answer! only conceptually correct.

  4. IndianJewel says:

    =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

  5. [...] other article discussed calculating averages that change.  It mentioned use for this tool in stock trading, as the average of various stocks [...]

  6. Doug Jenkins says:

    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 :)

  7. Twee says:

    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!

  8. Chandoo says:

    @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…

  9. FortMcmurray says:

    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

  10. FortMcmurray says:

    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

    • Chandoo says:

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

  11. FortMcmurray says:

    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?

    • Chandoo says:

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

  12. Amber says:

    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.

  13. Chandoo says:

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

  14. shamsuddin says:

    hello..,
    can you help me to calculate the sales of Jan 09,Feb 09,Mar 09 ..ect.using this formula..in the above example ?

  15. Chandoo says:

    @Shamsuddin.. you mean calculating sales values from the moving average?

  16. Cordell says:

    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?

  17. Chandoo says:

    @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)

  18. Ahmed says:

    You are THE MAN
    thank you for this website
    God Bless

  19. alejandro says:

    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.

  20. Chandoo says:

    @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/

  21. Geoff says:

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

  22. Geoff says:

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

  23. Chandoo says:

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

  24. Chandoo says:

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

  25. DND says:

    I need to calculate a 12-month rolling average that will encompass a 24 month period when completed. Can you point me in the right direction as too how to get started? My data is vehivle miles and starts on B2 and ends on B25. Help!!

    Bigdog

  26. TGCRequiem says:

    Chandoo, this is a great formula for what I am using except I am trying unsuccessfully to make the formula conditional. I have a spreadsheet, see links below, that tracks all rounds of disc golf played by friends and myself.

    http://thegrammatoncleric.com/DiscGolfTracker.htm
    http://thegrammatoncleric.com/DiscGolfTracker.xls

    I’ve already got it setup to calculate each of our overall averages and each of our averages on specific courses. What I am trying to do now however is also setup a moving average based off our 5 most recent rounds. Once more data has been entered I will change it to 10, but for now 5 will be just fine. I can get the moving average to work, but I cannot figure out how to add conditional restrictions. IE I want for example just the last 5 rounds that were played by Kevin. After that I will want just the last 5 rounds played by Kevin at the Oshtemo course.

    The code I’m using is below.
    Code for Cell C9 is listed below.
    =IF($B9=0,””,IF($B9<6,AVERAGEIF(DiscRounds!$A$2:$A$20000,$A9,DiscRounds!$M$2:$M$20000),AVERAGE(OF FSET(DiscRounds!$M$2,IF(DiscRounds!$A$2:$A$20000=$A9,COUNT(DiscRounds!$M$2:$M$20000),"")-5,0,5,1))))

    Essentially if there are 0 rounds it leaves the cell blank. If there are 5 or fewer rounds it just uses the average of all rounds. Finally, if there are 6 or more rounds the code then uses your AVERAGE function from this post. After trying many things however I am uncertain how to conditionally pull the last 5 rounds so that it only pulls the last 5 rounds of the individual named in cell $A9.

    The formula I am referencing is NOT currently in cell C9 on my spreadsheet that is linked. I just have been testing it there.

  27. Chandoo says:

    @DND: use the following formula in cell C13 onwards =AVERAGE(B2:B13) and drag down.

    @TGCRequiem: If you have excel 2007, you can use the AVERAGEIFS formula to achieve this.

  28. Vipul says:

    Hi Chandoo, You can use negative numbers in offset for rows/cols/height/width. So instead of doing -3 in rows and then +3 in height, you could just do it at once as -3 in the height. :)
    Surprised that no one (out of close to 10K readers) pointed that yet on this year old article of yours!!

    • Chandoo says:

      @Vipul… Since writing this article I have realized that we can use negative numbers in OFFSET. I will probably include this idea in an upcoming tutorial.

  29. Pap says:

    I need to calculate a 12-month rolling average to roll up into a separate summary sheet. My monthly data is entered (and will be entered) in cells B4 thru B26 of sheet 2, and there are some blank cells for future entries. (Each month, I enter data on the next available row). I need to calculate the average of the last 12 months (entries) in that column and have that figure entered on cell C4 of Sheet 1. Can you help me?

  30. Chandoo says:

    @Pap… Use the formula =average(offset(Sheet2!b4,counta(Sheet2!B4:B26)-12,0,12,1))

  31. Gav says:

    Hi all,

    Just adapted this to fit the data I was working on after going through a few interpretations. This is a fantastic site and a great explanation. Thanks for the big help Chandoo!

    As a heads-up to other users to help them save time if they are doing something similar I was working with stock data in the format; date (A:A), open(B:B), high(C:C), low(D:D), close(E:E) and fitted the formula in the following order (copied from cell H:15):

    =AVERAGE(OFFSET($E$2,COUNT($E$2:E15)-$H$3,0,$H$3,1))

    where $H$3 is the reference cell that determines the length of the moving average lookback period.

  32. Tracey says:

    Hello,
    I was just wondering if you could use the offset function within a pivot (calculated field)?
    If so, how would that be done?

    Many thanks – Your site is gem!!!

    Tracey

  33. Eoin says:

    Hi
    This discussion I have found very helpful and I have solved a moving average problem adapting the formula mentioned above =AVERAGE(OFFSET(G4,COUNT(G4:G314)-30,0,30,1))
    by Geoff.

    However I notice this works when cells contain data, but if a cell has a 0 value this will be included in the average calculation unless those 0 values are removed . Is there a way to write the same formula but include something to exclude (not hide) any cells containing a 0 value?

  34. Chandoo says:

    @Tracey… I do not think you can use OFFSET in Pivot calculated fields.

    @Eoin: You can modify the formula to omit zeros like this:

    =SUM(OFFSET(G4,COUNT(G4:G314)-30,0,30,1))/ COUNTIF(OFFSET(G4,COUNT(G4:G314)-30,0,30,1),"<>0")

    PS: I have not tested it, but it should work without too much tweaking.

  35. Eoin says:

    Thanks very much, this works fine and gives a truer average.

    Eoin

  36. Snooo says:

    Hello Chandoo,

    I am strugling with Pivot table trying to compile in a field the moving average (on last six month values) of another field.

    Have you any idea on how top proceed?
    thanks

  37. Lawrence Dodge says:

    This works great, except I always get an error that says “The formula in this cell refers to a range that has additional numbers adjacent to it.” I have to manually click on “Update formula to include cells”, how can I have this done automatically? Thanks in advance
    Lawrence

  38. Chandoo says:

    @Snooo: I am not sure if you can do moving averages in pivot tables natively. But you can do this by adding extra calculation right next to pivot table.

    Assuming your pivot’s values start from B5 (that is B5 is the first cell to have a value other than labels)

    In any free cell write =AVERAGE(OFFSET(B5,COUNTA(B:B)-COUNTA(B1:B5)-6,0,6,1))

    This will get you the average of last 6 values in the pivot (assuming you dont have totals / grand totals on)

    @Lawrence: Since we are referring only 3 cells (or 4 or 5 etc.) at a time, you might get this error. You can turn off this notification by going to excel options > formulas and then unchecking the box that says “formula omits cells in adjacent region”.

  39. Snooo says:

    Chandoo,
    thanks for this workaround, I already applied it following your hints on the previous posts of this page. The point is that is not as flexible as a field in a Pivot Table.
    If you discover an alternate Pivot-native solution for moving-averages, do not hesitate to propose it.
    thanks again.

  40. Stefanie says:

    I am trying to have a rolling 56 day window of data. I don’t need an average but rather the count and was trying to use countif function to count how many samples in the 56 day window were positive.

    Is there any way to use count instead of average?

    • Chandoo says:

      @Stefanie… Welcome to chandoo.org

      You can do that like this:

      Assuming your data is in column A, from cell A2:A301. And you want to findout how many positive values are there in each of the 56 values starting A2.
      in B2 write =Countif(a2:a57,”>0″)
      Now drag B2 thru B301 to auto fill.

  41. Stefanie says:

    We have two data results put in daily and would like to see our rolling window for every 56 days. I have done the countif function previously, and can get the 56 day results for that current window, if i plug that in everyday. We want a function so that it can be a rolling window and as we go to the next day the information will change. I know I can drag down to get that day’s currents 56 day window, but was hoping there was a way to only have the most recent information showing and not everyday’s.

    I have a whole sheet of information on one sheet and then on another “results” tab I am trying to break down the information in the current 56 day window.

  42. Hui... says:

    @Stefanie
    Have you tried Pivot Tables, which can summarise data easily

    Do you always have 2 entries per day?
    Do you ever have any days with 1 or 3 entries ?
    What Columns is your Data and Dates in ?

  43. Stefanie says:

    I haven’t tried Pivot Tables and haven’t done too much work previously with them. The Date is in column A and the Result for the test (Pos. or Neg.) is in column F. And there are only two samples everyday.

  44. Hui... says:

    @Stefanie
    If the first row of a date is in an even row try in
    A2: =IF(ISEVEN(ROW()),COUNTIF(F2:F115,”>0″),””)
    or Odd Row try in
    A3: =IF(ISODD(ROW()),COUNTIF(F2:F115,”>0″),””)
    and copy either down

    or a bit more flexible
    A2: =SUMPRODUCT(($A$2:$A$300>=A2)*($A$2:$A$300<=A2+56),($F$2:$F$3000))

  45. lepromatous says:

    Hey man – thanks for the site – this is the only rolling average calculation that I have actually gotten to work. One question though – what modifications are needed if the data are arranged horizontally – if I use this formula:

    =SUM(OFFSET(B1,COUNT(B1:B312)-12,0,12,1))/ COUNTIF(OFFSET(B1,COUNT(B1:B312)-12,0,12,1),”0″)

    only if the data are in B1:M1

    Thanks!

  46. Chandoo says:

    @lepromatous: Welcome to chandoo.org and thanks for comments.

    what modifications are needed if the data are arranged horizontally

    You mean, you have data in 311 columns starting B1 thru LB1?

    In that case, the formula =SUM(OFFSET(B1,0,COUNT(B1:LB311)-12,0,1,12))/ COUNTIF(OFFSET(B1,0,COUNT(B1:LB311)-12,0,1,12),”0″) should work.

    You just need to alternate the arguments of OFFSET to get it. Here is a tutorial on offset to help you get started.

    http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/

  47. lepromatous says:

    Thanks Chandoo, and thanks for the offset link… that will be great.

    I think I understand what is going on now. Unfortunately I couldnt get the formula to work. For example, If I have data in A20:N20 and want a rolling 12 months, editing the above gave me:

    =SUM(OFFSET(A20,0,COUNT(A20:N20)-12,0,1,12))/ COUNTIF(OFFSET(A20,0,COUNT(A20:N20)-12,0,1,12),”0?)

    Excel gives me a ‘too many arguments’ error and highlights the 12 at the end of the sum(offset statement.

    Im sure this is a stupid error on my part but any help is greatly appreciated!

  48. Hiaanko says:

    I am hoping you can help! I am working on multiple spreadsheets and within these spreadsheets multiple pages. I am trying to calculate a 4 week moving sum on one page that moves based on the date entered on another page. So for one calculation I am using 3 pages: the one where the calculation is housed, the date page and the data page. The ultimate goal of this calculation is to have it calculate two 4 week moving sums’ and divide the results into a percentage. This calculation will be used hundreds of times.

  49. chanel says:

    how would you calculate a seasonal trend, here’s the example (using quarters):

    Assume there is a trend and a seasonal factor at work in the series so that the underlying model is O=TSI. Use the moving avg approach to forecast sales for each quarter of year 4.
    1 121
    2 144
    3 117
    4 112
    1 165
    2 192
    3 153
    4 144
    1 209
    2 240
    3 189
    4 176
    1
    2
    3
    4

  50. Hui... says:

    @Chanel
    Assuming your data is in A2:B13 with the quaters in Column A and Data in Column B with B13 having 176 you have 2 quick approaches
    1. in B14: =AVERAGE(B2,B6,B10) and copy down
    2. in B14: =AVERAGEIF($A$2:A13,A14,$B$2:B13)

    Note the second method is auto scalable in that if you keep adding data below row 13 you can use the same formula and just drag it down as you go

  51. Hui... says:

    @Lepromatous
    If you have Data in A20:N20 and want a 12 period moving average why not simply
    =AVERAGE(A20:L20) and copy across

  52. Hui... says:

    @Lepromatous
    If you want to be able to vary the average period use:
    =AVERAGE(OFFSET(A20,0,0,1,E1))
    where E1 will have the number of periods

  53. help! says:

    I am trying to code an annual 1 year rolling average of data — that is based on a set of dates matched to a set of reset rates. I need to effectively get the average of today’s date – minus 365 days. I’d like to reference the date function today() in the formula if possible.
    Offset doesn’t work.

    Any ideas?

    Thanks! Lori

  54. Vipul says:

    @ help! =average(if((A1:A5000=today()-365),B1:B5000,””)) Ctrl+Shift+Enter where A1:A5000 has your dates and B1:B5000 has respective data

  55. Vipul says:

    @ help! For some reason the formula came in incorrectly in the post.. Use this =average(if((A1:A5000>=today()-365)*(A1:A5000<=today()),B1:B5000,”")) Ctrl+Shift+Enter where A1:A5000 has your dates and B1:B5000 has respective data

  56. Bahati kachawi says:

    Hi mr. Vipul
    Can you please help how to interpretiate the 5-per moving average. why do we use 5-per moving average?

  57. jasmin says:

    Hi Chandoo,

    I am stuck on one of the similar problems since months. I am new to excel and it would be really great you if could help….

    I want to have moving averages in this fashion:

    Say I have data in range of A2:A30,
    then, B2 will be average(A2:A6), B3 should be average(A6:A10), B4 should be average(A10:A14)…and so on…

    Thanks very much in advance…

  58. Vipul says:

    @ Bahati I’m sorry; I did not get your question!
    @ Jasmin Use this formula in B2 and then drag it down =AVERAGE(OFFSET($A$2,(COUNTA($A$2:A2)-1)*4,0,5,1))

  59. Andrew says:

    Hey Purna,
    btw, bad link below.
    Cheers.
    —————————————————————————————-
    http://chandoo.org/excel-formulas/count.html
    —————————————————————————————-

    Internal Server Error

    The server encountered an internal error or misconfiguration and was unable to complete your request.

    Please contact the server administrator and inform them of the time the error occurred, and anything you might have done that may have caused the error.

    More information about this error may be available in the server error log.

    Apache/1.3.33 Server at chandoo.org Port 80
    —————————————————————————————-

  60. Brisky says:

    Can I add a query in with the rest….I have sales data from the past year broken down into date and day of the week which I add to each week, I am wanting to calculate the moving average per day over the the last year from todays date.

    For reference columns are:
    A1=Date, B1=Day, C1=sales1, D1=sales2, E1=sales3, F1=Total Sales

    I want to know average total sales for Mondays in the past 365 days.

    It would be even better if I could arrange this to display as a drop down box so I choose the day and it reports into an adjacent cell the current daily average.

    Many thanks to anyone who can solve as its beginning to drive me insane!

  61. DHB4 says:

    Hi,
    I’ve found this page really helpful already but i’m a bit stuck!
    I’m trying to make a sheet for my cricket team to work out each players average. i also want to do their last 5 games average. i can do this with this formula and if they dont miss a match. if they do miss a match i need to leave it blank. how do i get around this? is there a tutorial? my data will range from E2 through to E25 with the possibility of any one missing.

    EG. E2 = 5, E3 = blank, E4 = 24, E5 = 16, E6 = blank, E7 = blank, E8 = 21, E9 = 12.

    I am greatful for any help please!!!

    Thanks
    D

  62. Jared says:

    I have what seems to be a simple problem. I have 12 columns of data (H9:S9) and need to calculate the rolling 12 month average in T9. The formula needs to count zeros in the average. Any suggestions?

  63. Lost says:

    I have a problem that I can’t seem to get to work. I have a chart with data in columns across a row. Each column is a work day of the year. It starts at B3 and proceeds across the row for subsequent days. I am trying to average the last 10 days with data. Some days have no data and my formula in those cells leaves the cell blank. How can I get my formula to average the last 10 cells with data in it?

  64. Hui... says:

    @Lost
    Try the following UDF
    Open VBA Alt F11
    Insert a Code Module, Right Click on your project and Insert
    Paste the following in
    =====
    Function AveUntil(ByRef Target As Range, Optional Numb As Integer = 1) As Variant
    Application.Volatile
    Dim j As Integer, i As Integer
    Dim cumm As Double
    j = 0
    i = 0
    If Target.Column - Numb < 1 Then
    AveUntil = "Decrease Numb"
    Exit Function
    End If
    Do Until j = Numb
    If Target.Offset(, -i).Value < <>> "" Then
    cumm = cumm + Target.Offset(, -i).Value
    j = j + 1
    End If
    i = i + 1
    Loop
    AveUntil = cumm / Numb
    End Function

    =====
    To use:
    In a cell type =AveUntil(cell Ref, Numb)
    eg: =AveUntil(C20,10)
    This will start at C20 and count back along the row until it finds 10 Valid numbers.

    If you get a “Decrease Numb” error decrease Numb as the current Column – Numb will be less than A

  65. Lost says:

    @Hui

    I have a compile error:syntax error when I try to execute the formula. The line that is highlighted is
    If Target.Offset(, -i).Value “” Then

    Can you advise?

  66. Hui... says:

    @Lost
    Manually re-type all the ” characters

  67. Lost says:

    @Hui
    After manually retyping all the ” characters I get a compile error: Expected: Then or Go To
    I added = after Value and I do not get an error. The line now reads

    If Target.Offset(, -i).Value =”” Then

    I do get a #Value! error in the cell I am working in.

    Here is the data I am working on for row 3

    CL=14
    CM=5
    CN
    CO
    CP
    CQ
    CR=15
    CS=11
    CT=15
    CU=15
    CV
    CW=11
    CX=11
    CY=10
    CZ
    DA=10

    My formula, =AveUntil(DA3,10), is in DA17. The result should be 11.7

  68. Hui... says:

    The line
    If Target.Offset(, -i).Value “” Then
    should read
    If Target.Offset(, -i).Value < <>>”” Then

    and check that the ” are correct: retype them to be sure

    I have updated code above also

  69. Lost says:

    @Hui,

    Thank you so much! It all works and my data looks great!

  70. Azrold says:

    Hi..
    Anyone know how to work out a moving average for a series of time data so it does not overlap?
    I have some air monitoring equipment which records data every 10 seconds, but i want the average of the minute, then the next minute then the next etc.
    cheers!

  71. Julie says:

    Hi, I’m sure there is something listed above that is suppose to help, but I’m still new to excel and am feeling overwhelmed. I just got a new job and I’m tryin to make a good impression, so any help woud be great!

    I have data for each month in 2009, 2010 and 2011 going across and multiple rows of this. Every month at the beginning of the month I need to calculate the sales % of the previous year. Currently my formula is =SUM(AG4:AR4)/SUM(U4:AF4). Example: Current month is March. Info I need is sales total from March 2010-February 2011 divided by March 2009- February 2010 and it works great, but it’s too time consuming to have to change it every month. Is there a way I can get the formula to automatically change at the beginning of the month? I don’t know if I did a very good job explaining this or not…

    • Chandoo says:

      Hi Julie,

      Congratulations on your new job.

      You can drag your formula sideways (to right for eg.) and it shows the %s for next month automatically.

  72. Julie says:

    No, what I need is for the formula to change each month. I have January 2009 through December 2011 boxes going across with data in them. =IFERROR(SUM(AG4:AR4)/SUM(U4:AF4),”0″)

    Next month I need for it go from calculating the sum of 03/10 data to 02/11 data divided by 03/09 data to 02/10 data and change to 04/10 to 03/11 data divided by 04/09 data to 03/11 data. =IFERROR(SUM(AH4:AS4)/SUM(V4:AG4),”0″)

    What I need is a formula that can refer to the current date and know that on the 1st of each month, it needs to switch the formulas over for the next previous 1-12 months divided by the previous 13-24 months. I’m not sure if that makes sense. Basically I use this formula about 8 times on one sheet and I have about 200 sheets.

  73. Julie says:

    Sorry for the double posting and thank you on the congrats! :-)

  74. Julie says:

    What I need: If the current date is greater than the 1st of the month then the entire cell references to calculate the sales % of prev year needs to move to the right by one column?

    This is what I’ve come up with…=IF(P1>=N1,(SUM(AH4:AS4)/SUM(V4:AG4)))
    p1 is current date
    n1 is 1st day of month
    AH4:AS4 is data from 03/10-02/11
    V4:AG4 is data from 03/09-02/10

    Part I’m having issues with: How do i make it so that the formula knows exactly what 12 sections to grab and how to get to automatically change at the 1st of the month.

  75. Chandoo says:

    @Julie… You can use OFFSET formula to solve this.

    Assuming each column has one month, and first month is in C4 and current date is in P1

    you can write,

    =SUM(offset($C$4,,datedif($P$1,$C$4,”m”),1,12))/SUM(offset($C$4,,datedif($P$1,$C$4,”m”)-12,1,12))

    The above formula assumes that each column has months in Excel date format. You may want to tweak it until it produces right result.

    For more info on OFFSET read – http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/

  76. Julie says:

    This is probably extremely simple and I am making it more complicated than I need to, but you wrote, “The above formula assumes that each column has months in Excel date format.” I’ve been struggling to do this without having it turn my data into dates.

  77. Chandoo says:

    @Julie… What I meant is, the row number 4, where you have month names, should contain this data -

    1-jan-2009
    1-feb-2009
    1-mar-2009

    etc.

    Like the file here: http://img.chandoo.org/playground/julie-moving-average-question.xlsx

    Also, I notice few errors in my formula. The correct formula should be,
    =SUM(offset($C$5,,datedif($C$4,$P$1,"m")+1-12,1,12)) / SUM(offset($C$5,,datedif($C$4,$P$1,"m")+1-24,1,12))

    The above formula assumes dates are in row 4 and values are in row 5.

    In fact, it would be lot easier for you, if you see the file. The URL is http://img.chandoo.org/playground/julie-moving-average-question.xlsx

  78. Julie says:

    I think that is exactly what I needed. Thank you thank you thank you so much!

  79. Laura says:

    My problem is very similar jasmin’s (61) and Azrold (74). I have disgusting amounts of data, from D:2 to D:61400 (and correspondingly in E and F, I’ll have to do the same thing for these columns as well). I’m trying to find the average for batches, such that D2:19, D20:37, D38:55 and so on – clumping 18 rows together and then finding the next average without re-using any previous row. I’d also have to likely do this for every 19 and 20 clumps as well, but an example using 18 is fine.

    Could you annotate the formula you post? I’m a little confused on what the last 4 numbers mean in the COUNTA part. Thank you so much, this is going to make my life so much easier!

  80. Hui... says:

    @Laura
    This is easily done with Average and Offset
    .
    Assuming you are doing this in Col J and are averaging Col D
    J2: =AVERAGE(OFFSET($D$1,(ROW()-2)*J$1+1,,J$1))
    Where J1 will have the number 18 for a moving total of 18 numbers
    Copy down
    Row 2 will average Rows 2-19
    Row 3 will average Rows 20-37
    etc
    .
    You can also add labels in say Col H
    H2: =”Rows “&(ROW()-2)*$J$1+2&” – “&(ROW()-1)*$J$1+1
    Copy down
    .
    I have mocked this up at:
    https://rapidshare.com/files/1923874899/Averages.xlsx

  81. Laura says:

    Thank you so much, Hui! That’s more than what I wanted it to do! : D

  82. Really nice talking here. Love to see exactly what I was searching. Thanks to matrix of Google.
    I am using Google documents, that is more efficient than MS excel. You can use it any where anytime. Actually I was searching above formula to add on my project planning, growth, sales till end of month.
    I have a question here,
    Whether I can use this function in Google docs too?

    Thanks
    B Pandey
    Shabd Technologies Pvt. Ltd.

  83. xyz says:

    @Pandeyji – You should try that in google docs and then ask if there is a problem getting it to work.. FYI – it does work.

  84. JTD says:

    Hello,

    I have one column of numbers. I want to create a formula that will allow me to input a variable number into a cell that will average that many cells.

    If the variable number = 5, then I want an average of the first 5 cells. If I change 5 to 10 I want to see the average for the first 10 cells and so on.

    Any ideas?

    Thanks….

  85. Hui... says:

    @JTD
    I assume your numbers are in A2:A100
    and the number 5 is in B1
    =AVERAGE(OFFSET($A$1,1,,B1))
    Adjust ranges to suit

  86. dv says:

    I have several columns of numbers. I would like to get a 10-row rolling average of each column of numbers so that as I add another row of numbers, the average will be automatically updated to include my new row & exclude the oldest row. Is this possible? Thanks in advance!

  87. Hui... says:

    @DV
    Add a column for your rolling average
    go to the 11th Row and insert
    =IF(D11=””,””,AVERAGE(D2:D11))
    Copy this down as far as you need
    as you add data below the existing rows the new rolling average will show

  88. dv says:

    Hui…,
    Thanks. Going one step further…do you know if there is any way to have one cell be the rolling average for each column? I’d like to have an automatically updating graph that is linked to the automatically updating rolling average cell when more data is put in.

  89. Hui... says:

    @DV
    Just repeat the above
    Typically if you have data Columns A..T use the corresponding AA..TT as the cumulative or rolling average columns

  90. John says:

    I have a set of prices in column B…I would like to b able to take an average over N periods prior to a specific date…for example:

    on 9/21 i have a price of $10….if N = 6…i want to average the current price and the previous 5 days’ prices…

    but also, if i were to change N to 8 i want it to average the current price and the previous 7 days’ prices…

    ive tried almost all the examples here and haven’t found one that works for this scenario…

  91. Hui... says:

    @John
    I’m going to assume you have dates in Column A
    If so try this
    =SUM(OFFSET($B$1,MATCH(D1,A:A,0)-$F$1,,$F$1))/$F$1

    Where
    D1 = End Date
    F1 = No. of Days
    If your worried about people putting in small dates or large No days
    this will catch that
    =IFERROR(SUM(OFFSET($B$1,MATCH(D3,A:A,0)-$F$3,,$F$3))/$F$3,"Error")

  92. John says:

    i’m not quite understanding D1….wouldn’t D1 be a function of F1 if F1 is the period….?

  93. Hui... says:

    “I would like to b able to take an average over N periods prior to a specific date”
    D1 is your specific date

    If that isn’t what your after please clarify

  94. STEVE says:

    Hello Hui, I was following your conversation with John from sept 29th, and I have something similar that I want to do, but am having a problem. My dates are 365 in total, spanning from D1 TO OA1. My numeral data is in D2 to OA2. I cant seem to figure out how to change the formula to fit. Everything else that you mentioned to john is applicable. I will keep trying to fix it, thanks for your help, Steve

  95. Hui... says:

    @Steve
    =SUM(OFFSET($A$2,,MATCH(A3,1:1,0)-$A$4,,$A$4))/$A$4

    Where
    A3 = End Date
    A4 = No. of Days

  96. STEVE says:

    Hello Hui! WOW, thanks for the info so quickly! Will try to understand what I was missing, thanks agian, Steve

  97. STEVE says:

    Hello Hui, interesting links under your bio! Thanks for posting. I forgot to mention, is there a conditional function that I could incorporate that would filter out cells with no data, so that the lookback average only considers cells with data? Thanks. Also I am not sure what purpose $A$2 serves as an offset reference. Thanks agian, Best, Steve

  98. STEVE says:

    Hello Hui, I tried some things that arent working, this is the latest. =(SUM(“D2:ND2″)/COUNTIF(“D2:ND2″,”0″)SUM(OFFSET($A$2,,MATCH(B4,1:1,0)-$B$5,,$B$5)))/$B$5
    The D2:ND2 are all the cells that are considered, including ones that are blank.

    I also figured out that the $A$2 offset reference acts as the touchstone for the calculations occuring in the aformentioned row. Thanks agian, Best,Steve

  99. STEVE says:

    Hello Hui, I also tried this. This made more sense to me, but no go.
    =SUM(OFFSET($A$2,((SUM(“D2:ND2″)/COUNTIF(“D2:ND2″,”0″)),MATCH(B4,1:1,0)-$B$5,,$B$5)))/$B$5
    Thanks, Steve

  100. Randy says:

    Trying to get a rolling monthly average when the cells I’m trying to average actually come from another input sheet and already have a link in them. I can’t seem to get the monthly average to display right as it keeps trying to divide the total by 12, rather than only the months that already have. Anyone have a simple formula that would work in this situation:

    Thanks,
    Randy

    January ’11 $100,000
    February ’11 $120,000
    March ’11 $-
    April ’11 $-
    May ’11 $-
    June ’11 $-
    July ’11 $-
    August ’11 $-
    September ’11 $-
    October ’11 $-
    November ’11 $-
    December ’11 $-

    YTD Summary $220,000
    Monthly Average #NAME?
    12

  101. Hui... says:

    @Steve
    =SUM(OFFSET($C$2,,MATCH(A3,D1:AO1,0)-$A$4,,$A$4))/$A$4

    @Randy
    For your Monthly Average try something like
    =Sum($Range)/countif($Range,"<>0")
    or
    =AVERAGEIFS($Range,$Range,"<>0")

    Change $Range to be the range where your $ are

  102. STEVE says:

    Thanks Hui,
    I apprieciate the response. During your absence the spread sheet design changed a bit, I got some help from Robert Pmika.
    He is quite willing to help as you are. We were able to solve the issue. For curiosity sake, as the sheet stands now; D1:ND1 are the 365 days of the year, D2:ND2 are the input numerical data for each date, B3 is the function that returns the variable average excluding blank cells, B4 is the start date, B5 is the lookback # of days. and the equation Rob wrote;
    {=AVERAGE(IF(INDEX($D$2:$ND$2, MATCH(B4,$D$1:$ND$1,0)): INDEX($D$2:$ND$2,MATCH(B4,$D$1:$ND$1,0)-B5) >1, INDEX($D$2:$ND$2,MATCH(B4,$D$1:$ND$1,0)) :INDEX($D$2:$ND$2,MATCH(B4,$D$1:$ND$1,0)-B5)))}
    Thanks to you both! Best Steve

  103. Randy says:

    Hui,
    It worked! Thank you so much!!! Appreciate the help.
    Randy

  104. kamarlon says:

    I want to write a formula that would use teh moving average to forecast next two periods ahead. I’m using monthly data. How would this formula be written?

  105. Hui... says:

    @Kamarton
    When you say you want to “use the Moving Average to forecast the next 2 periods”
    The Moving Average is not a forecast tool, it is a historical averaging tool that averages historical data over a specified period. Its use is to show historical trends while smoothing outliers in the data.

    Having said that you can calculate a Moving Average and then use that as the basis for forecasting.

    Typically add an extra column/row to your data and calculate the moving average in that.
    Then use a Trend or Forecast Function to extrapolate that data for the next few periods.

    Have you had a read of:
    http://chandoo.org/wp/2011/01/24/trendlines-and-forecasting-in-excel/
    or
    http://chandoo.org/wp/2011/01/26/trendlines-and-forecasting-in-excel-part-2/

  106. rxg says:

    Hi guys — I was reading the thread above, and am stuck on a similar situation, and was hoping someone could help me.

    I have a spreadsheet where I would like it calculate the 52-week high (cell B1) and low (cell C1) for a stock price, but the 52 week “range” should continue to change as I change the date in cell A1.

    So basically, if I enter Nov 11, 2011 in cell A1, cells B1 and C1 should help identify the max and min for the past 365 days (52 weeks or Nov 11, 2010), and if I change the date in cell A1 to Nov 12, 2011, then the max (cell B1) and min (cell C1) should go back 52 weeks from that date (essentially Nov 12, 2010).

    Can someone help me generate a formula for both the max and min?

    I’ve been messing around with this way too long and am now almost wasting my time as I did not come to a conclusion, but listed below is my best guess…..which got me nowhere.

    =INDEX(B9:B50000,IF(MATCH(A1,A9:A50000,1),VLOOKUP(A1,MAX(A9:B50000),2,1)))

    Thank you in advance for your time and any help you can provide will be greatly appreciated.

  107. Marina says:

    Hi guys– I cannot seem to be able to come up with the right formula for the following task. I’m building an option pricing model and need to come up with a moving average price. In Column A I have dates; in Column B – corresponding Option Prices; $D$2 – some specific date; F2 – Option Trade Date; F3 – Option Expiration Date. What i need the formula to do is calculate the length (in days) of the option contract (Expiration Dt minus Trade Dt); lookup the date from $D$2 in column A and calculate a moving average price from that date for the life of the option. So, using an example, if we have 12/1/11 in $D$2 and it’s a 20-day option, i need the formula to go to column A, find 12/1/11 and calcualte an average price from 12/1/11 to 12/21/11 (20 days). Hope this is not too confusing. Thank you in advance!

  108. Jenn says:

    Hiu Chandoo! I am trying follow this example with a set of data I have and I cannot get it to work. All my data is between E2:AZ2. Not all the months are in yet. Right now it is only filled to AB2. But I want to set this up for future calculations. In D2 I have
    =AVERAGE(OFFSET(E2,COUNT(E2:AZ2)-12,0,1,12))
    However, if I do an average of the last 12 data points (=average(Q2:AB2)) I get a very different answer. Can you tell me what I’m doing wrong?
    Thank you!

  109. Jenn says:

    Hi Chandoo!
    I am trying to use this example in on of my projects. I want a rolling average from E2:AZ2, but I currently only have data up to AB2. The formula I have is:
    =average(offset(E2,count(E2:AZ2)-12,0,12,1))
    However, when I put in =average(Q2,AB2) I get a very different number. I’d like to not have to manually update the average every month. Can you tell me where I’m going wrong?
    Thank you!

  110. Fe says:

    Help Please
    I have weekly sales data in one row – ie C8 to BC8
    I am trying to do a Average of the latest 12 weeks of data and latest 4 weeks of data, that automatically takes the latest 4 / 12 wks of data, without me changing the formula, each week when another set of data is entered.
    It seems the above formula works for data in a column but not a row, but I tried the horizontal formula (which is so much more complicated) couldn’t get this to work either and see someone else also couldn’t get the horizontal conversion to work.
    Many Thanks

  111. RS says:

    My spreadsheet has the 12 months horizontally beginning with B1:M1. Data (in %’s) begins in B2 and corresponds with each month. I can’t find a formula that will calculate the latest 3 months average (horizontally) and update as I enter a new month’s % (as in your example “Calculate Moving Average of Latest 3 Months Alone”). Any suggestions would be greatly appreciated.

    • Hui... says:

      @RS
      it will be something like:
      =AVERAGE(OFFSET($B$2,,COUNTA($2:$2)-4,,COUNTA($2:$2)-1))

      • RS says:

        Thanks so much!! With a slight modification I got the formula to calculate correctly:

        =AVERAGE(OFFSET($B$2,,COUNTA(B2:M2)-3,,3))

        Now my next dilemma is trying to calculate a moving average of the latest 3 months when I need to average for example data from cells L2(November) & M2(December) from another worksheet titled 2011 and cell B2(January) in my current worksheet titled 2012.

  112. Jan says:

    Hi Chandoo!

    I was just wondering if you could help with my problem as I’m making myself ill trying to resolve it!

    I have a row of manually entered averages in E56:Q56.
    I would like to show what the subsequent average for each cell is e.g.E56 = 80%, F56 =E56+F56, G56 = Result of E56+F56+G56 and so on.
    In the last cell R56 I would like to show a summary of the totals.

    I’m sorry if it doesn’t sound clear but at the moment in R56 I have an average of averages but this is not what my Manager wants!

    Many thanks in advance for any help that you can give,
    Kind regards, Jan.

    • Hui... says:

      Hi Jan

      If you have data in E56 & F56

      You can’t have a formula in F56: =E56+F56

      That will give you a circular reference error

       

      Why not add another row

      E57: = E56

      F57: =if(F56<>0,average($E56:F56),0)

      Copy F57 across

       

       

  113. Jan says:

    Thank you for this Hui, I spoke again to my Manager today who said what she is looking for is the volme x the percentage added across the spreadsheet and a summary at the end of the row.

    E.g I have the following:-

    F49:Q49 showing the total volume of calls received

    F52:Q52 showing the average handling time for calls received as a percentage  

    She would like (F49*F52) then in the next cell (F49*F52) + (G49* G52) then in the next cell (F49*F52)+(G49*G52)+(H49*H52) and so on and so forth. 

    She would then like a summary in the end cell showing this formula for the whole row divided by the total volume of calls received .

    I am completely stumped and very, very grateful for your help!  

    Kind regards, Jan 

    • Hui... says:

      @Jan

      In any cell in Column F (not in Row 49 or 52) put:

      =SUMPRODUCT($F49:F49,$F52:F52)

      Then copy across

       

      • Jan says:

        Hui – How on earth did you do that!? It’s like wizardry! How do you people know these things??? You make it look so simple!

        I had the biggest formula you’ve ever seen in each cell going across the page and still couldn’t get it right :’(

        I am immensely grateful (and in awe) for your help again.

        God bless,

         Jan :) 

  114. Alice says:

    i got this to work (?)
    =AVERAGE(OFFSET(B20:B22,0,0,3,1))
     

    b20
    52
    50

    b21
    65
    37.33333333

    b22
    33
    41.66666667

    b23
    14
    37.66666667

    b24
    78
    65.33333333

    b25
    21
    60.66666667

    b26
    97
    72

    b27
    64
    79.66666667

    b28
    55
    69.66666667

    b29
    120
    80.66666667

    b30
    34
    62.33333333

    b31
    88
    76.5

    b32
    65
    65

  115. Josh says:

    Hi! Im hoping you can help me with this.
    I have a table that gets added to daily. I would like to show the average of the last 20 entries. The answers above all seem to work with regular pages, but with a table it wont work right. Thank you!

  116. Oran says:

     
    I am beginner trying to:
     
    1. structure a spreadsheet that will then be used to
     
    2. determine the optimal period for my moving average, within the range of a 5 day moving average to a 60 day moving average.
     
    Each cell represents the number of sales for that day, ranging from 0 to 100. I would prefer that each month of daily sales be in a new column.Currently I have 3 months of data, but obviously that will grow.
     
    So can you please tell me how to set up the spreadsheet and then the appropriate formulas (and their locations)?
     
    Thank you very much,
     
    Oran
     

  117. Jan says:

    Hello again Hui,

    I am struggling yet again with the same spreadsheet you helped me with earlier.

    As beore, I have the following rows of monthly manually entered data:

    Volume of Calls
    Calls Answered
    %age   of calls abandoned
    Average handling time

    My line manager would now like 2 rows beneath these showing (by using formula):
    Average speed of answer
    Average abandoned time   

    And as if that wasn’t enough, she would like, for both rows, a summary cell at the end of the 12 months showing the yearly figure :’(        

    Many thanks again for any help you are able to give,

    Jan     

  118. Kimber says:

    I am using the vertical version for calculating a moving average. I am stumped when I need to calculate a 6-period moving average. My data starts in column c and the 6-period and 3-period averages are two columns to the right of the last period of data. I add a column for each month, so I currently adjust the formula manually each month:
    =AVERAGE(EC8:EH8)

    My most recent attempt (that failed) is:
    =AVERAGE($C$6,COUNT($C$6:EH6),-6,6,1)

    Please provide an explanation of why this didn’t work when responding so I can understand how to create future formulas.

    Thank you so much,
    Kimber      

    • Chandoo says:

      @Kimber… Welcome to Chandoo.org and thanks for commenting.

      I think it is not a good idea to place averages in right most column as it keeps moving. Instead you could modify your sheet so that moving average is placed at left most column (and this will stay there even if you add extra columns to the right).

      No matter where the average cell is, you can use this formula to calculate the moving average.

      =AVERAGE(OFFSET($c$6, 0,COUNTA($c$6:EH6)-6,1,6))

       

  119. Ben Elliott says:

    Afyter having read the whole of this thread I can see I’m going to need a combination offset, match, count and averageif but I’m not sure where. My problem is as follows:
    Each month there are over 100 people reporting activity-
    Column A is their name, Column B is the month, Column C is the year and Columns D through M is their activity in several categories. I need to find their 3 month and six month averages and display that in another worksheet although I could have them displayed in Columns N and O if needed. I use a pivot table to produce sums and total averages but it won’t handle moving averages. Any pointers would be greatly appreciated.
    Thanks,
    Ben

  120. Kevin says:

    AVERAGE(OFFSET(D75,-(Mov_Avg-1),0,Mov_Avg,1)

    This will average the last Mov_Avg number of rows including itself (take out the -1 if you want it to not include itself).
     
    D75 is the cell that this formula is referencing (my data was very long)

    “Mov_Avg” is how big you want the moving average to be (I assigned this as a named cell (select the cell, Formulas –> Defined Names –> Define Name)  You can make variable names in a spreadsheet to avoid always having to use row/column.)

    This starts from the current cell (D75 in this case), goes up Mov_Avg-1 rows, over 0 columns, selects Mov_Avg nuber of rows, with 1 column.  Passes this to the average function.

  121. Raed says:

    Thanks Chandoo
    Can we do this rolling using Pivot Tabels.

  122. Hendo says:

    Hi all, I’m a basic excel user and am still having problems with calculating a three month moving average horizontally. My spreadsheet contains data in cell B2 through to M2 and sales data for each month is automatically populated. I would like N2 to show the three month average on the date uploaded for the last three months only, excluding all zeros. If anyone could help me it’d be greatly appreciated, I’ve tried numerous formulas in the above thread however nothing seems to be working. Thanks for your help

    • RS says:

      Try this moving average formula:
      =IF(ISBLANK(D3),””,AVERAGE(OFFSET(B3,,COUNTA(B3:M3)-3,,3)))
       
      Good luck!!
       

      • RS says:

        Clarification:
        I was using row 3 for the example. The part of the formula …….-3,,3))) calculates a rolling 3 month average. If it was …..-6,,6))) the formula would calculate a rolling 6 month average, etc. Hope this helps.

  123. Hendo says:

    Hi RS, thank you that’s working perfectly! Really appreciate your help.

  124. Matt says:

    Hi! I read through every post, but haven’t been able to get this working correctly… How do we calculate the moving average of a percentage?
    This is calculated weekly…
    Column A – accts met
    Column B – accts sold
    Column K – closing %
    Column D – 2 week moving average of the closing %?
     
    Example of week 1 and week 2
    Column A, row 7 is 25 and row 8 is 1
    Column B, row 7 is 1 and row 8 is 1
    Column K, row 7 formula is 1/25 (4%) and row 8 is 1/1 (100%)
    Column D – The formula in a prior post gives me an answer of 52% 2 week avg, but that’s not correct… it should be 2/26 (7%)
    =IF(ISERROR(AVERAGE(OFFSET(K7,COUNT(K7:K26)-2,0,2,1))),””,AVERAGE(OFFSET(K7,COUNT(K7:K26)-2,0,2,1)))
     
    What do i need to change in that formula to use columns A & B instead of the % column K?
     
    Thank you!!
    Matt

    • RS says:

      You are trying to average averages, which doesn’t work. Try this simple formula beginning in D8:
      =IF(ISBLANK(B8),””,(B7+B8)/(A7+A8))
      Copy and paste the formula down to D26. This should give you a moving 2 week average. Remember to format column D as a percentage with how ever many decimal points you want.
       

  125. Mike says:

    Hi,

    I’m pretty much an excel neophyte. I just stumbled across your site & am looking forward to perusing it at length in the months ahead.

    I’m trying to calculate a 3 month moving average of expenses & cannot figure out what I am doing wrong. Even after reading this article and the post on offset I’m not sure I understand the formula.

    In my sandbox, I have:

    Column A – Months
    A2:A17=Sept 2012 – Dec 2013
    Column B – Total monthly expenses
    B2:B8 (B8 because March is the last completed month) – Those totals are 362599,372800,427317,346660,359864,451183,469681
    Colum C – 3 Month Moving Average.

    I put the following formula in C4 (To start calculating in Nov of last year, just for grins).

    =AVERAGE(OFFSET(B$4,COUNT(B2:B17)-3,0,3,1))

    Since there are only three months in the data set at that point, I would assume it calculates the “moving average” of the first three months. The formula comes up with 469,681. When I average the first three months, I come up with 387,572.

    What am I doing wrong or misunderstanding?

    Thanks for the help and for putting this website together.

  126. Mey says:

    Hi Chandoo!
    You have one really useful project here, tons of thanks!

    In the very beginning of this thread Shamsuddin asked something similar to what I need, “reverse” calculation of values from the moving average. Maybe it’s stupid, but I can’t come up with any ideas except for figure-by-figure lookup.
    If possible – please advice with this article’s data, to get the concept.
    Actually, I’d be happy to get anything, as google was of no use )

    Once again – thank you so much for this site!

    • Hui... says:

      @Mey

      I’m not really sure what you mean by reverse calculating a moving average

      Can you explain what your trying to do/achieve
      Posting a sample file might help also
      Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

      • Mey says:

        Hi Hui,
        I mean, I have a column of figures (e.g. monthly shipments), which are calculated as moving average based on another data set (e.g. monthly manufacturing output).

        Smth like this:
        (A1) Jan Feb Mar Apr May Jun
        Mfg
        Ship 100 500 450 600 600 700
        Where Ship =average(B2:C2)

        I know only shipments volumes, and have to find out respective mfg volumes. Generally speaking, the question is how we can find initial data with only MA on hand?

        Suppose, this thread may not be the one for asking this (if you agree – maybe you know where to ask). It’s just that Shamsuddin’s question was the most relevant result out of 10 google pages :)

        • Hui... says:

          @Mey
          To calculate the original data from a Moving Average (MA) you need two MA’s eg a 9 and a 10 day MA or 1 MA and 1 piece of data

          From these you can recalculate the previous result

          But if you have a formula =Average(B2:C2)
          you should have access to the data

          If it is a 2 day MA like your formula above
          MA=Average(B2:C2)
          MA=(B2+C2)/2
          if you know B2
          C2=(2*MA)-B2

          If you have a set of data you can share I can give a better solution
          Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

  127. David Buffalo says:

    I think where I am losing it here is on the standard definition of offset. I have yet to see any website (including Microsoft’s) that shows exactly how all of these parameters fit together. I see that this function has 5 inputs, but on the Microsoft website, there are only four). I still do not see the logic of the function (and with what I have calculated by hand, I see that the offset formulae give errant values for the moving averages in the examples I have created. This is a mess :).

  128. Harshith says:

    Thanks a lot, just what I was looking for.

  129. Amanda says:

    I need to calculate a moving average of the last 3 months. However, if one of the months contains 0%, I want it to ignore that and take the last month that didn’t have a zero.

    For instance, my data is this: April = 100%, May=0%, June=97%,July=98%, August=0%.

    My formula is only looking at June July and August, but since August has a 0 I would like it to look at May June and July. And since May has a 0, I would like it to look at April, June and July.

  130. Great website. Forgive this question. I used to be an Expert in Lotus 123 decades ago, but I find Excel somewhat backwards in its progressions to Lotus 123, so I am starting over with Excel 2010.

    I am a logical person and I try to understand what the formulas do when I use them. I notice that there are not but 14 sales figures in column B, yet somehow we are counting from B4 to B33. I tested the formula out using:

    +AVERAGE(OFFSET(B4,COUNT(B4:B14)-3,0,3,1)) and I get the same result as if I used +AVERAGE(OFFSET(B4,COUNT(B4:B33)-3,0,3,1)). My first rule of old school spreadsheet creation is never to build a data table larger than the data provided if it is static (that is, not expanding in data). As a result, I have no real clue as to how OFFSET works. Is there a clear explanation of OFFSET with a singular example of it being used outside of the average and all by itself?

    The reason I came here is to build a spreadsheet model that would use iterative calculations to find the best fit for profit data (that is maximizing profit) when the a short moving average of the cumulative profit curve (or equity curve) crosses OVER the longer term moving average of the equity curve. I find nothing that allows expansion of moving averages from 3 periods to say 100 periods (for both averages). By using the MA cross over to determine which trades to take, one can find an optimal level of profit to run the model from (which could be tweaked when the model is reoptimized). I can find nothing in most Excel books that cover this, and this kind of calculations should be relatively simple to pull off. Where could I find such information?

    Thanks again for the wonderful website.

  131. Arieanna says:

    I have a question.

    I already have a 3 day moving average that I was given in my problem. Is it related to the average of stocks. The questions says that you have 1 stock that you PLAN on selling on day 10. My 3 day moving average is an integration from [a,b] where a=t and b=t+3 at any time. If you want to find the price you expect to sell the share for, do you integrate from [6,9]? [9,11]? [7,10]??? Do you want the far end of day 10, the middle of day 10, or leave day 10 out? I am not sure what time frame to put this 3 day average between. Again, my function represents up to day 14, but I need the price at day 10.

  132. ivan Santos says:

    Im looking to see the moving average for a call center. im trying to find the index for every month for a full year. i only have 2 years worth of data and im wanting forecast out for 2014 in quarters. can i use this method for this?

  133. VIJAY says:

    I have a problem in average, I want to calculate the average of highlighted rows only in coloumn F on colomn G which also has highlighted blank cells

  134. Akash gulati says:

    Hi chandoo,
    Here is one question.
    A Column B column (Month target) C column (Avg sale/day)
    Saleman1 5000 5000/30
    Saleman2 10000 10000/30
    Saleman3 20000 20000/30
    Saleman4 250000 250000/30
    Saleman5 30000 30000/30

    If on daily basis i have to track it .What kind of formulas i have to put in so as to make it more clear.If some body doesn’t reach the daily average how can i show this impact on overall sale requirment on daily basis.

  135. hurab says:

    Thanks Chandoo for your help

  136. Milan says:

    Hi,

    My try for dynamic moving average. Length chooser is in cell D3 and series start at B4. Not a perfect one, just wanted to share with you guys.
    Calculates “upfront” average – means that you need to have data below B4 in order to work (could be reworked to “look” at before data with negative offset row value).

    =AVERAGE(INDIRECT(ADDRESS(ROW(B4),COLUMN(B4))&”:”&ADDRESS(ROW(OFFSET(B4,$D$3,0))-1,2)))

    Cheers and thank you Chandoo for helping us excel at Excel.

    Regards,
    Milan

  137. Clara says:

    Hi chandoo,

    I have a file containing the following aranged in columns: A-Items Names; B-weekly demand; C-Date received.

    There are multiple items in Column A, what is the best approach to calculate moving average for weekly demand?

    Thanks for your help

  138. Jessica says:

    Hello genius!

    I need help!!!

    I have an excel file which contains sales data (by week) for a certain products.

    I am trying to come up with a formula that will tell me the average of the last (or previous) weeks from a certain starting point.

    Say for example I am trying to forecast my sales for the week of September 29, 2014 and per my boss, the forecast should equal the average of the 12 weeks directly prior to September 29, 2014…but then let’s say my boss changes his my, and now the forecast should equal the average of only the 4 weeks prior to September 29, 2014 (instead of 12 weeks)….is there someway I can do this?

    I have all my produts in different rows, and the columns represent the sales on a weekly basis for the last 3 years.

    Basically I will have to do this for over 1000 products, and the formula needs to be flexible because I’ll be constantly changing inputs (eg. i might need the average of 12 weeks prior to a certain date, but then i might also need the average of 10 weeks subsequent to a certain date, etc)…. can you help me??????? PLEASE, PLEASE, PLEASE!!!

    thanks :)

Leave a Reply