Graph Creation in Excel
 

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

Graph Creation in Excel
 

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" wrote:
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

Graph Creation in Excel
 

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.

"Duncs" wrote:

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

Graph Creation in Excel
 

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

Duncs

On Jul 7, 2:36*pm, BobT wrote:
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.



"Duncs" wrote:
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- Hide quoted text -

- Show quoted text -