Time Range as one of two criteria in Excdel

[tyemm]

I am trying to chart the occurrence of events, as a function of their time of day and their location. I'd like to be able to group all the times into 24 discreet hourly segments, so that an event at 01:14 is grouped with an event at 01:54 but not with 00:54, etc. The events occur in locations with identifiers such as 2M and 4C, etc.

Thus a table:
Time Location
7:55 2M
8:30 2M
10:26 4C
11:12 4C
11:40 4C
11:53 4C
12:12 4C

For each location (2M, 4C, etc.) I would like a COUNT of all the events that occurred in each hour range of the day.

Question: can I do this with one formula alone, or must begin by groupin the times, then separating (possible pivot table?) the locations, etc.?

 

[AnotherHiggins]

A pivot table will summarize all your data very quickly.

Walk through the wizard (post back with any questions), then after you have the table, Right Click on any hour and select Group and Show Detail > Group. Deselect Month and select Hour.


(Hint: Make Time your row and Location your column. Then use either in the data section and make sure that it says "Count of")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[tyemm]

Wow that was quick--
More importantly, succinct, thorough and correct.
Thank you anotherhiggins. I need to find a pivot table tutorial so I won't miss easy shots like that!

 

[AnotherHiggins]

I'm glad I could help. The Pivot Table is an amazing tool.

I honestly haven't had time to read through this, but Chip Pearson tends to have great information. So have a look at this tutorial:

http://www.cpearson.com/excel/pivots.htm

Also bear in mind that if the number of rows in your source data will change, you can use a Dynamic Named Range as the source for your Pivot Table.

If you aren't familiar with Dynamic Named Ranges, have a look at faq68-1331.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[tyemm]

I am making progress, and still poking around for further tutorials on pivot tables & charts.
I'm hesitant to refresh data as this invariably plays havoc with the charts, and the suggestion above for grouping (e.g. by hour, in a column of discreet times) is not something I can get to work reliably for me. Meaning: I right-click on one time/cell and choose Group, but I don't get the unit (hour,day, etc.) option, but only something like between 0.0 and 0.99. I can't say I have never gotten this Group and Show Detail to work, but rarely, so I'm not in the position to try a macro, at least not just yet.

What gives here?