Formula for calculating moving averages

[greykitten]

I need an Excel function that will calculate a moving average on monthly data for time periods of 3,6,12 & 36 months. The average for each of these time periods must not include the current month's data. It must only include the prior 3,6,12 & 36 month data. I've been struggling with the AVERAGE(OFFSET...) formula without any success. Could someone please help me derive a formula so I don't have to manually adjust the cell references each month? This would save me a ton of time. Thank you in advance. I greatly appreciate any help you can give me. Here's a link to an example showing what I'm trying to do.


https://hotfile.com/dl/179398004/b7bcfff/Rolling_Average_Calculation.xlsx.html


Thanks so much,

greykitten

 

[Trader273]

This should get you started in the right direction. Offset can be a little tricky, but it's very powerful in order to make your excel dynamic. Chandoo has great free tutorials on this site, so I would look at that.


https://www.dropbox.com/s/1hczqes6agx413j/Rolling%20MAs.xlsx

 

[SirJB7]

Hi, greykitten!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s), maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.

http://chandoo.org/forums/topic/an-if-question


Just advise if any issue adapting it to your file, which I haven't checked.


Regards!

 

[NARAYANK991]

Hi ,


Can you check your file here ?


http://speedy.sh/fFQdt/Rolling-Average-Calculation.xlsx


I have defined a named range Curr_Mnth.


Narayan

 

[greykitten]

Hello,

Thank you so much to everyone who replied! I greatly appreciate your help. I searched throughout the forum and the search engine for hours but was unable to find any help on this.


To NARAYANK991, may I ask how you defined the "=Curr_Mnth" formula? I saw nothing like this in the list of Excel functions in Excel 2010. If I'm understanding correctly, it's possible to create Excel formulas? If so, how did you do it? This was by far the simplest solution.


Once again, thank you so much to everyone. I really appreciate your time and assistance. This makes my work so much easier!


Take care,

greykitten

 

[NARAYANK991]

Hi ,


This is a built-in feature of Excel called named ranges. What you do is give a label to an Excel range ( this can be just one cell or multiple cells , extending to even the whole worksheet ) , and thereafter use the label exactly as you would an Excel address.


Suppose you have a value 5 in cell A1 ; suppose in cell B1 , you put in a formula =A1 ; what this does is put the cell B1 equal to the cell A1. Thus , B1 ends up having the same data that is in A1 i.e. 5.


Now , suppose you go into the Excel Name Manager , and create a new named range ; suppose you call it Just_Another_Name ( names cannot contain the space character , hence the underscore ; of course , you could have named it JustAnotherName , which would make it harder to read ) , and in the Refers To box enter the formula =Sheet1!$A$1 ; you could now enter the formula :


=Just_Another_Name


in B1 , and B1 would display the value 5 ( or whatever the cell A1 contains ).


This formula is a very simple formula ; in theory , you could make your formula as complex as you need to.


The named range Curr_Mnth is having the following formula in the Refers To box :


='Rolling Average Source'!$CA$1


The cell CA1 itself has the following formula in it :


=COUNTA($G4:$BZ4)


You could have put this formula in the Refers To box , and done away with a physical cell ( CA1 ) called Curr_Mnth ; you could still use the named range Curr_Mnth wherever you would use an Excel address reference.


The following link is a very gentle introduction to this fascinating and extremely powerful feature of Excel :


http://www.homeandlearn.co.uk/excel2007/excel2007s7p6.html


Narayan