Graph Creation in Excel

[Duncs]

All,

I don't know if this is possible, but here goes.

I have a number of entries in a spreadsheet, showing me date / time of
access and a site. For example:

01-07-2008 09:15:34 Aberdeen
01-07-2008 09:15:37 Aberdeen
01-07-2008 10:10:10 Glasgow
etc.

What I need to show is a graph that lists the number of users, per
site and per hour that accessed the system. So, in the above example,
two columns / entries would be shown on the graph with Aberdeen having
a count of 2 for the period 09:00 until 09:59 and Glasgow showing 0.

Then, in the second column, Aberdeen would show 0 for the period 10:00
until 10:59 and Glasgow would show one.

Is this possible?

TIA

Duncs

 

[BobT]

You first need to do a grouping (a.k.a. subtotal) of the data. Once that is
done (with a Count on the time range - which needs to be formatted to allow a
group by hours (i.e. drop the mintes)) then you can chart away and show
bars/lines per region by the hour.

 

[Max]

One play - use a helper col to extract the hour from the Time col,
then create a pivot table

Data is assumed split into 3 cols: Date, Time, Loc in cols A to C
from row2 down

In D1: Hour (a label)
In D2, copied down: =IF(B2="","",HOUR(B2))
Then create a pivot table on cols A to D, drag n drop "Loc" in ROW & in
DATA, "Hour" in COLUMN, to get the desired results

A quick sample for the above:
http://www.freefilehosting.net/download/3j9ll
Extract Hour n Pivot.xls
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik

 

[Duncs]

Thanks to all for your replies. I've got it sorted now.

Duncs