How to get and automatically updated monthly average based on data from specific dates.

Hi All,

I am going to try and explain this as best I can.
The collumn most to the left (A) contains the date (yyyy-mm-dd). The data is recorded weekly and ranges from 2007 - present. In column X i have sales figures. For example:

Column A
2012-05-22 What i wish to have, is a column next to column X, that automatically writes down the average figure for each month. given that some months contain more
2012-05-29 dates than others, it is not possible to simply repeat and average calculation per 4 rows. So far i have been manually taking the average in a row next to it,
2012-06-05 but given the range of data, this will take a very long time (considering i have to do it for several sets of data). I hope someone can help!
2012-06-12
2012-06-19
2012-06-26
2012-07-03
2012-07-10

Column X
923
923
943
950
970
1000
1000
1030


Thank You
Dave K.

Newdoverman, thanks for replying so quickly, but i have run into a problem.

For the first helper column (column z), excel tells me that there is an error in the first equation ( =TEXT(MONTH(A2)&YEAR(A2),"yyyymm") ), when i type it in. Is this notation compatible with 2010 excel? Also, the data that i am averageing starts on row 173 so i also tried the formula with A173, but still gave me an error. Do you think you could spot the problem?

Thanks

Hi,

I managed to make it work when I looked at the workbook you posted.

Thanks again for the help.

Hi again. I thought I'd ask you directly since you helped me earlier, as opposed to re-explaining the question. Simila
r to my post before, I want automatically updated averages, but slightly different to the normal monthly average. I have two things I would like done with similar data on different files.
On the first one, I would like a monthly average just like before, but this time it should be taken from the 16th (of the first month) to the 15th (of the second month). Given that my data is weekly, I would like then to average from the week containing the day closest to the 16th having a date above or equal to it --> to --> the week containing the day closest to 15th having a date below or equal to it. So for example, given that the data was taken for the following dates:
04/04/2007
11/04/2007
18/04/2007
25/04/2007
02/05/2007
09/05/2007
16/05/2007
23/05/2007
30/05/2007
06/06/2007
13/06/2007
20/06/2007
27/06/2007
04/07/2007
11/07/2007

The average would be first taken from 18/04/2007 to 09/05/2007 and then next would be taken from 16/05/2007 to 13/06/2007 etc.

For the second one, I want quarterly averages but (for some reason) the company wants them bunched Dec-Feb, Mar-May, June-Aug, Sep-Nov.

Please tell me if i'm being unclear with my explanation.

Thanks,
DK

I'm afraid i have "Insufficient permission" to view your file. If you can't change that, don't worry, im gonna make a new thread.

Thanks

Never mind, it works