10
2
I have a list of date/timestamps, and I would like to plot these to a chart in Excel. What I'm looking for is a chart that shows "events per hour" or something similar. Can this be done easily if I just have one column of data (the list of timestamps)? I'm using excel 2007 and looking at two data sets. One is 56k events and the other 750, both over the span of a few days.
Segfault
Posted 2010-04-09T16:23:21.593
Reputation: 413
9
Give your column of timestamps a name, in the first row, say "Timestamp".
Now create a Pivot Table with Timestamp used twice - once in the Values area (Count) and again in the Row Labels area.
You can then select grouping on the Row Labels as days, hours, minutes, whatever.
Then select Options | PivotChart.
Mike Fitzpatrick
Posted 2010-04-09T16:23:21.593
Reputation: 15 062
1Thanks Mike, this is great. I'm just can't quite figure out how to get it to group by hours. I selected the dates in the pivot table, and the "Group Selection" button on the ribbon activated, but when i click it it just throws all of the dates into "Group 1" and there is no Group By dialog or options anywhere. – Segfault – 2010-04-16T16:51:52.253
3Don't select ALL the dates. Just have one of the dates selected before you click on Group Selection.
Also, if your data spans days and you want to differentiate them, you should select group by days as well as hours. – Mike Fitzpatrick – 2010-04-17T01:08:03.677
1
Several thoughts -
First, search histogram in Excel's built-in help. You should have the ability to install the Data Analysis add-in which has a function to do just that.
Otherwise, if you're willing to shell out the $30 - Addins.com has an addin for this purpose. (I have not used it so I can't say what is better or worse compared to the built-in functions.)
Either way - you may run into trouble with charting limitations (I'm not sure what 2007's limits are, but 2003 only will allow 65k rows of data and other restrictions on points in a chart).
Finally, a plug for 'R' - this software could very simply import arbitrarily large data sets and give you histograms with the data you're looking for. As an added bonus, it's open-source freeware and can import many data file formats. See 'R' at cran.r-project.org
JDB
Posted 2010-04-09T16:23:21.593
Reputation: 675
1
Make a pivot table, group date field by hour as explained in http://www.contextures.com/xlpivot07.html, then insert a chart based on pivot table.
Toc
Posted 2010-04-09T16:23:21.593
Reputation: 1 663
Did you mean 750 or 750k? – Toc – 2010-04-11T10:48:53.673
I meant 750, thanks. It's a subset of the other data. – Segfault – 2010-04-16T16:52:45.217