easy way:Converting Weekly Data into Monthly Averages

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

Take a look at the sumif and countif functions. sumif/countif will give you
an average.
HTH,
Gary Brown

"Kaine" <Kaine@discussions.microsoft.com> wrote in message
news:7FD45235-D0D7-4415-A915-0CBE914BD7BD@microsoft.com...
> 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.

Pivot Table

Headers on your two columns - DATE & VALUE
Select all your data, do Data / Pivot table & Chart Report, hit Next / Next
/ Finish

Drag DATE to the ROW fields
Right click on any of the dates and select GROUP & SHOW DETAIL
Select Months (already selected) and Years (Just click it as well) - Both
will appear in Blue - Hit OK
On the table, drag the YEARS field that just appeared into the top of the
table where it is marked COLUMN fields
Drag VALUE into the DATA area - Right click on any of the values, choose
field settings and from the list on the left of the dialog box, select
AVERAGE.

Done.

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

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

"Kaine" <Kaine@discussions.microsoft.com> wrote in message
news:7FD45235-D0D7-4415-A915-0CBE914BD7BD@microsoft.com...
> 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 for the example Ken,

I have spent this morning playing around with the dynamic updates and it
doesn't deal too well with gaps of data (which i have a few due to lack of
data and formatting etc).

I also found it difficult to use on the charts that i have created myself
and updating the 50 or so tables manually will be quite time consuming. Any
suggestions around these issues?

For the weekly table i had a vlookup formula which looked at the
corresponding year at the top and the corresponding week at the side, it then
looked at the large columns of data to pull these out. It didn't seem to mind
the gaps in the data.


=VLOOKUP(B$3&" - "&$A4, Data'!$A$4:$EH$550,74,FALSE)
Where B38 is the week number
A4 is the year
It then looks up in the data the corresponding week number & year and
returns the results from column 74.
Is there an easy way to put an average in this formula to work out monthly
averages?
Appreciate any help.


"Ken Wright" wrote:

> Pivot Table
>
> Headers on your two columns - DATE & VALUE
> Select all your data, do Data / Pivot table & Chart Report, hit Next / Next
> / Finish
>
> Drag DATE to the ROW fields
> Right click on any of the dates and select GROUP & SHOW DETAIL
> Select Months (already selected) and Years (Just click it as well) - Both
> will appear in Blue - Hit OK
> On the table, drag the YEARS field that just appeared into the top of the
> table where it is marked COLUMN fields
> Drag VALUE into the DATA area - Right click on any of the values, choose
> field settings and from the list on the left of the dialog box, select
> AVERAGE.
>
> Done.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "Kaine" <Kaine@discussions.microsoft.com> wrote in message
> news:7FD45235-D0D7-4415-A915-0CBE914BD7BD@microsoft.com...
> > 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.
>
>
>