Per hour-of-day summary chart

[Keke]

Is there an easy way to create a chart showing the total number of
requests per hour of day from the following raw data (simplified
example, real data consists of thousands of log entries)?

Timestamp | Number of transactions
===============================================
2006-05-01 1:15:54 pm | 41
2006-05-01 1:15:55 pm | 33
2006-05-01 1:15:56 pm | 40
2006-05-01 1:18:08 pm | 3
2006-05-01 3:40:12 pm | 1
2006-05-01 8:01:33 pm | 2
2006-05-02 1:52:04 am | 1
2006-05-02 2:01:33 pm | 1
2006-05-03 3:11:11 pm | 1

What about I'd like to categorize the transactions in the chart by
using a third column in the raw data called "type of transaction"?

I've been trying to Google around but apparently am not savy enough
with Excel to even come up with the correct keywords...

TIA,
Keke

 

[Dave Peterson]

If those values in the Timestamp column are really dates/times, you could use a
pivottable.

If they're not really dates/times, then I'd convert them to dates/times.

Try this against a copy of your worksheet
select the Timestamp column
data|text to columns
fixed width
But remove any lines and don't add them yourself.
and finish up the wizard.

When I did this, excel was smart enough to convert the data into real
dates/times.

Then I could select my range--both columns (timestamp and transaction count)
data|Text to columns
follow the wizard until you get a step with a button with Layout on it.
click that Layout button
drag the timestamp header to the row field
drag the Number header to the data field
and finish up the wizard.

Now rightclick on any entry with a date/time (in column A)
choose Group and show detail
Then choose Group
Group by Days and hours.

=======
When you get the third column, you can drag that "type" to the column field (or
to the row field below the timestamp) to see how the report would "pivot".

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Keke]

If those values in the Timestamp column are really dates/times, you could use a
pivottable.

Thanks Dave, worked like a charm!