software

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

gt; 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
PeltierTech.com/
_______

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

Thanks. I hadn't thought of pivot tables. What do you mean by quot;grouping
the date fieldquot;?

Thanks,

Heidi

quot;Jon Peltierquot; wrote:

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

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
PeltierTech.com/
_______

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

-Heidi

quot;Jon Peltierquot; wrote:

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