easy way:Converting Weekly Data into Monthly Averages

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