Graphing weekly/monthly averages

[Guest]

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

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

 

[Guest]

Jon,

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

Thanks,

Heidi

 

[Jon Peltier]

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

 

[Guest]

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

-Heidi