Thanks GG,
From the limited knowledge of pivot tables i don't think they will suit.
(Feel free to correct me if i'm wrong)
I should have mentioned before that the monthly averages have to update
automatically once data is entered into the datasheet. For example as each
weeks data is entered it will be averaged into the monthly data column. At
the moment Febraury will have four entries (4 weeks). Next week (1st week of
March) will have 1 average for the month of March, the week after 2 weeks
average etc etc.
I'm not aware of how pivot tables can update automatically within a
spreadsheet. I have about 50 columns of data that has to be updated
automatically each week form the data source into weekly & monthly averages &
then onto charts, so its a hefty file that i want as little work as possible
on it apart from entering base data.
Cheers.
"GusGG" wrote:
Hi Kaine,
I think your best bet would be a pivot table.
It does all of the things you mentioned and more..
The only thing you wll need to do is include a Month column in your sheet
and you will be set..
If you don't know about Pivots I can help you to some degree.
GG
"Kaine" wrote:
Does any one know an easy way to convert a series of weekly data into their
respective monthly averages.
I have a large range of data in weekly format:
1/2/99 3.54
8/2/99 5.41
15/2/99 2.10
through to...
25/2/05 4.10
How can i get that data into a table which looks like:
1999 2000 ....... 2005
Jan 5.12 1.24 ...... 5.28
Feb 2.14 3.54 ....... 1.79
Mar etc etc
....
Dec 2.45 4.8 ........ #NA
The monthly average table needs to look up the respective months in the
weekly data and average them into the corresponding cell (ie Jan-1999 equals
5.12 from the 4 or so weeks of data).
I am thinking vlookup function, but am unsure how to add average when
looking up a series of dates in a lookup function.
I already have a similar table which looks up the week number of the date
and puts the corresponding weekly data into the table against its week number.
The weekly data may also have returned #na for graphing purposes, is there
anyway i can accommodate this in the formula.
I would appreciate some help.
|