easy way:Converting Weekly Data into Monthly Averages

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.

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.

Hi Kane,
Pivots absolutely update based on the underlying data.. Most of the work
you are performing in the columns can be done in the pivot..

If you like you can send me a limited amount of the data with all the
respective headings and I will set up a pivot on the sheet that may fit your
needs.

If you decide to do so, send it to: li_speedyg @ Yahoo.com <---Delete
the spaces

Cheers,
GG
"Kaine" wrote:

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

See the example I gave you - it does exactly what you need it to. On top of
that you can use a dynamic range as the soiurce that will automatically pick
up new entries as you enter them. All you will have to do is right click
on the table and select refresh. If you really really needed it to update
automatically without so much as having to refersh the table then that can
be done quite easily with code, but i would have thought overkill in this
instance - but it can be done quite easily.

Dynamic source range:-
http://www.contextures.com/xlPivot01.html

An Excellent intro to Pivot tables
http://peltiertech.com/Excel/Pivots/pivotstart.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip>