I need guidance as I step into the dark land of the Evil Empire.

[johno]

I need some help with Microsoft Excel.

I'm trying to get trend report generated.

I have a excel spreadsheet that has 17 months of *daily* data in a simple table:

2003-11-14 50000
2003-11-15 55230
2003-11-16 62000
...
2005-03-19 70000
2005-03-20 71855
2005-03-21 71200

I can chart it easily enough, but I get a very wiggly graph and I'm more interested in seeing the weekly or monthly totals.

Short of creating the weekly/monthly sub-totals, is there an easy way to create sums or charts, with data subtotaled based on date?

Comments

[kyburg.livejournal.com]

Not unless you want to load that data into a database, and then run reports off it - which is what I'd suggest.

Then you could plot points on a cumulative date calculation.

[neongraal.livejournal.com]

Try playing with a PivotTable and PivotReport.

I find that most advnaced reporting tasks can be solved (or approximated) using a PivotTable/PivotReport

[khanfused.livejournal.com]

When you're doing that -- you just need to make sure that Excel is treating his date field AS a date field, instead of ten bazillion data labels. Because if it's doing the latter, it won't average out the line into a curve -- it'll try to plot every single damned line, AND print the date "label" at the bottom of the graph.

I still haven't figured out how to fix that one.

[mdlbear]

My inclination would be to print the whole thing out and munge it with a Perl script. Then import it back into Excel for the graphing.

Doing the monthly totals in Perl would be trivial; doing the weeklies might be simpler if you convert the dates into Unix time_t (seconds since 1970-01-01), which Perl cn do pretty easliy.

[succ33d.livejournal.com]

I can't be of any help, I dont know anything about Microsoft Excel, sorry.

well, I know a place you could ask...

[nagasvoice.livejournal.com]

I get email reports from a place called Woody's Watch, who seemt o know what they are about on various OFfice topics. (It's particularly amusing to read their rants about the security holes in IE, for instance.)
Many moons ago, I posted some questions and got fairly quick responses.
Woody's Lounge has different forums for various parts of Office in general, including one for Excel in particular. This is the Excel forum where you could post your question. Don't know if now you have to join the thing first, sorry.
http://www.wopr.com/cgi-bin/w3t/postlist.pl?Cat=&Board=xl

[elizabear.livejournal.com]

Gah, I used to be an Excel power user, doing graphed reports every month as part of my job. But that was 5 years ago, well before the baby ate my brain.

I've just scanned O'Reilly's EXCEL HACKS book - there's no good way in there to do what you want.

I'd go with charting the sub-totals - it doesn't take long to input a range for a sum function.