Graphing weekly/monthly averages

I currently work with a very large data set that includes daily values for a
variety of variables (one day per row, different variables in columns B-H).

For various logistical reasons, the data are not kept in chronological order
by date. Thankfully, when graphing the daily values, Excel handles
out-of-order dates just fine when the x-axis is in date format.

However, now that the data set is getting so huge, I'd like to have
additional charts for weekly averages and monthly averages.

I can do this at the spreadsheet level if the data are sorted by date (but I
don't want to do that), but was wondering if this could be done at the graph
level. There are options under Format Axis>Scale that seem like this could
happen, but instead of averaging them, it graphs each point vertically above
the given week or month.

What is the best approach for generating weekly and monthly average values
and graphing them?

Thank you!
Heidi

Heidi -

> I can do this at the spreadsheet level....

Why not use a second sheet as a summary sheet, which serves as the source of
the chart data? You can use a pivot table to generate the weekly or monthly
numbers (by grouping the date field), or formulas (probably array formulas).
Neither approach requires the original data to be sorted.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Heidi" <[email protected]> wrote in message
news:[email protected]...
>I currently work with a very large data set that includes daily values for
>a
> variety of variables (one day per row, different variables in columns
> B-H).
>
> For various logistical reasons, the data are not kept in chronological
> order
> by date. Thankfully, when graphing the daily values, Excel handles
> out-of-order dates just fine when the x-axis is in date format.
>
> However, now that the data set is getting so huge, I'd like to have
> additional charts for weekly averages and monthly averages.
>
> I can do this at the spreadsheet level if the data are sorted by date (but
> I
> don't want to do that), but was wondering if this could be done at the
> graph
> level. There are options under Format Axis>Scale that seem like this
> could
> happen, but instead of averaging them, it graphs each point vertically
> above
> the given week or month.
>
> What is the best approach for generating weekly and monthly average values
> and graphing them?
>
> Thank you!
> Heidi

Jon,

Thanks. I hadn't thought of pivot tables. What do you mean by "grouping
the date field"?

Thanks,

Heidi

"Jon Peltier" wrote:

> Heidi -
>
> > I can do this at the spreadsheet level....
>
> Why not use a second sheet as a summary sheet, which serves as the source of
> the chart data? You can use a pivot table to generate the weekly or monthly
> numbers (by grouping the date field), or formulas (probably array formulas).
> Neither approach requires the original data to be sorted.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> "Heidi" <[email protected]> wrote in message
> news:[email protected]...
> >I currently work with a very large data set that includes daily values for
> >a
> > variety of variables (one day per row, different variables in columns
> > B-H).
> >
> > For various logistical reasons, the data are not kept in chronological
> > order
> > by date. Thankfully, when graphing the daily values, Excel handles
> > out-of-order dates just fine when the x-axis is in date format.
> >
> > However, now that the data set is getting so huge, I'd like to have
> > additional charts for weekly averages and monthly averages.
> >
> > I can do this at the spreadsheet level if the data are sorted by date (but
> > I
> > don't want to do that), but was wondering if this could be done at the
> > graph
> > level. There are options under Format Axis>Scale that seem like this
> > could
> > happen, but instead of averaging them, it graphs each point vertically
> > above
> > the given week or month.
> >
> > What is the best approach for generating weekly and monthly average values
> > and graphing them?
> >
> > Thank you!
> > Heidi
>
>
>

To group a field, right click on the field button in the pivot table, and
select Group and Show Detail from the popup menu, then select Group. Select
the appropriate period to group by in the dialog.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Heidi" <[email protected]> wrote in message
news:[email protected]...
> Jon,
>
> Thanks. I hadn't thought of pivot tables. What do you mean by "grouping
> the date field"?
>
> Thanks,
>
> Heidi
>
> "Jon Peltier" wrote:
>
>> Heidi -
>>
>> > I can do this at the spreadsheet level....
>>
>> Why not use a second sheet as a summary sheet, which serves as the source
>> of
>> the chart data? You can use a pivot table to generate the weekly or
>> monthly
>> numbers (by grouping the date field), or formulas (probably array
>> formulas).
>> Neither approach requires the original data to be sorted.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services
>> Tutorials and Custom Solutions
>> http://PeltierTech.com/
>> _______
>>
>> "Heidi" <[email protected]> wrote in message
>> news:[email protected]...
>> >I currently work with a very large data set that includes daily values
>> >for
>> >a
>> > variety of variables (one day per row, different variables in columns
>> > B-H).
>> >
>> > For various logistical reasons, the data are not kept in chronological
>> > order
>> > by date. Thankfully, when graphing the daily values, Excel handles
>> > out-of-order dates just fine when the x-axis is in date format.
>> >
>> > However, now that the data set is getting so huge, I'd like to have
>> > additional charts for weekly averages and monthly averages.
>> >
>> > I can do this at the spreadsheet level if the data are sorted by date
>> > (but
>> > I
>> > don't want to do that), but was wondering if this could be done at the
>> > graph
>> > level. There are options under Format Axis>Scale that seem like this
>> > could
>> > happen, but instead of averaging them, it graphs each point vertically
>> > above
>> > the given week or month.
>> >
>> > What is the best approach for generating weekly and monthly average
>> > values
>> > and graphing them?
>> >
>> > Thank you!
>> > Heidi
>>
>>
>>

Thank you! The group feature in the pivottable does exactly what I needed!

-Heidi

"Jon Peltier" wrote:

> To group a field, right click on the field button in the pivot table, and
> select Group and Show Detail from the popup menu, then select Group. Select
> the appropriate period to group by in the dialog.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> "Heidi" <[email protected]> wrote in message
> news:[email protected]...
> > Jon,
> >
> > Thanks. I hadn't thought of pivot tables. What do you mean by "grouping
> > the date field"?
> >
> > Thanks,
> >
> > Heidi
> >
> > "Jon Peltier" wrote:
> >
> >> Heidi -
> >>
> >> > I can do this at the spreadsheet level....
> >>
> >> Why not use a second sheet as a summary sheet, which serves as the source
> >> of
> >> the chart data? You can use a pivot table to generate the weekly or
> >> monthly
> >> numbers (by grouping the date field), or formulas (probably array
> >> formulas).
> >> Neither approach requires the original data to be sorted.
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Peltier Technical Services
> >> Tutorials and Custom Solutions
> >> http://PeltierTech.com/
> >> _______
> >>
> >> "Heidi" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I currently work with a very large data set that includes daily values
> >> >for
> >> >a
> >> > variety of variables (one day per row, different variables in columns
> >> > B-H).
> >> >
> >> > For various logistical reasons, the data are not kept in chronological
> >> > order
> >> > by date. Thankfully, when graphing the daily values, Excel handles
> >> > out-of-order dates just fine when the x-axis is in date format.
> >> >
> >> > However, now that the data set is getting so huge, I'd like to have
> >> > additional charts for weekly averages and monthly averages.
> >> >
> >> > I can do this at the spreadsheet level if the data are sorted by date
> >> > (but
> >> > I
> >> > don't want to do that), but was wondering if this could be done at the
> >> > graph
> >> > level. There are options under Format Axis>Scale that seem like this
> >> > could
> >> > happen, but instead of averaging them, it graphs each point vertically
> >> > above
> >> > the given week or month.
> >> >
> >> > What is the best approach for generating weekly and monthly average
> >> > values
> >> > and graphing them?
> >> >
> >> > Thank you!
> >> > Heidi
> >>
> >>
> >>
>
>
>