MrExcel Publishing
Your One Stop for Excel Tips & Solutions

how to make a graph based on start stop times


Posted by Robert on February 12, 2002 7:45 AM

I have the following:
jobid|jobtype|starttime|stoptime
111|order|2/12/02 9:22AM|2/12/02 9:23am
112|PO|2/12/02 9:23AM|2/12/02 10:00am
113|WT|2/12/02 9:24AM|2/12/02 9:35am
114|order|2/12/02 10:02AM|2/12/02 10:20am

I am trying to make the following type of charts:
1) Chart by 24 hour clock, in 1-5 min intervals, showing # of programs running at that time. This could be an avg, if not using 1 min.

2) Chart showing same as above, but also broken down by type (multiple lines).

I have tried everything that *I* can think of. Can someone help me out and point me in the right direction?

Thanks,
Robert


Posted by Skrappy on February 12, 2002 8:11 AM

The way that I would do this is by creating a histogram (tools: data analysis: histogram) and having bins of 5 minutes. This should take care of the problem. After excel creates the histogram, then you can plot it. If you have additional questions, I will check back later on today.

Posted by Robert on February 12, 2002 9:24 AM

Skrappy,
After looking at the histogram item..it is close. I am not sure how to create bins at 5 min increments though, after looking at the helpfile for it, it is a range (high end/low end) for the bins, and not an increment. I could be misunderstanding this though.

This is a example of the actual data:
33689382 oerd 2/12/02 9:22 AM 2/12/02 9:22 AM cr2
33124494 ICRTT 2/12/02 9:14 AM 2/12/02 9:17 AM dhs
33157437 edepp 2/12/02 9:13 AM 2/12/02 9:13 AM jpm
33200437 oerd 2/12/02 9:17 AM 2/12/02 9:18 AM cer

for input range, I am selecting the start/stop times only, for bin I left it blank (since it is supposed to use the top/bottom end of the data, and this was the result:

Bin Frequency
2/12/02 9:13 AM 2
2/12/02 9:14 AM 0
2/12/02 9:16 AM 1
2/12/02 9:17 AM 2
2/12/02 9:19 AM 1
2/12/02 9:20 AM 0
More 2

not sure why 9:13 has 2, and 9:14 has none.

Any help you give is truly appreciated.

Thanks,
Robert

Posted by Skrappy on February 12, 2002 10:19 AM

Robert,
The way you need to set up the bins is to create a column like the following:
2/12/02 9:00 AM
2/12/02 9:05 AM
2/12/02 9:10 AM

and fill these values down until you have your 24 hour range. These will become your bin values. When you now choose histogram it will count the values of your data whose values lie between two consecutive bin values. Then you select your start / stop times as your input range, and it should give you the correct values.


Skrappy,

Posted by Robert on February 12, 2002 11:15 AM

SO CLOSE! heh

Here is the output from the data, it isn't doing it quite right:
time histogram true count (by hand)
2/12/02 9:12 AM 0 1
2/12/02 9:13 AM 2 1
2/12/02 9:14 AM 0 1
2/12/02 9:15 AM 1 1
2/12/02 9:16 AM 0 2
2/12/02 9:17 AM 0 1
2/12/02 9:18 AM 2 1
2/12/02 9:19 AM 1 0
2/12/02 9:20 AM 0 0
2/12/02 9:21 AM 0 0
2/12/02 9:22 AM 0 1
2/12/02 9:23 AM 2 0
2/12/02 9:24 AM 0 1
2/12/02 9:25 AM 1 1
2/12/02 9:26 AM 0 1
2/12/02 9:27 AM 0 1
2/12/02 9:28 AM 0 2
2/12/02 9:29 AM 2 4
2/12/02 9:30 AM 6 2
2/12/02 9:31 AM 1 2
2/12/02 9:32 AM 0 2
2/12/02 9:33 AM 1 2
2/12/02 9:34 AM 2 2
2/12/02 9:35 AM 3 4
2/12/02 9:36 AM 6 0
2/12/02 9:37 AM 0 0

Here is the source for the data:
33157437 edepp 2/12/02 9:13:00 AM 2/12/02 9:13:00 AM jpm
33124494 ICRTT 2/12/02 9:14:59 AM 2/12/02 9:17:39 AM dhs
33200437 oerd 2/12/02 9:17:28 AM 2/12/02 9:18:03 AM cer
33689382 oerd 2/12/02 9:22:21 AM 2/12/02 9:22:30 AM cr2
33879897 poerr 2/12/02 9:24:51 AM 2/12/02 9:35:26 AM dtd
34112763 ares 2/12/02 9:28:43 AM 2/12/02 9:28:44 AM cer
34127763 ibris 2/12/02 9:29:10 AM 2/12/02 9:29:10 AM mdb
34081197 porii 2/12/02 9:29:12 AM 2/12/02 9:29:12 AM ejt
34096229 oerd 2/12/02 9:29:16 AM 2/12/02 9:29:17 AM vee
34208197 smrs 2/12/02 9:30:35 AM 2/12/02 9:32:39 AM bkh
34386494 smrs 2/12/02 9:33:22 AM 2/12/02 9:33:38 AM bkh
34438162 smrs 2/12/02 9:34:15 AM 2/12/02 9:34:29 AM bkh
34480835 smrs 2/12/02 9:35:00 AM 2/12/02 9:35:10 AM bkh
34407264 icrii 2/12/02 9:35:02 AM 2/12/02 9:35:06 AM jdw
34539437 glej 2/12/02 9:35:43 AM 2/12/02 9:35:44 AM ajc

Notice the histogram #'s are off. I think it is rounding some of the #'s up to the next highest minute, but that does not explain why it counts 9:13 twice, and for 9:16, there is no info (id: 33124494 is running during that time).

Thank you for the help you have given so far.

Robert

Posted by Robert on February 12, 2002 11:20 AM

explanation of that last output


Broken down to time from the bin range, histogram results, and third column is me counting the info by hand. BTW, the source file for this is around 10000 records/day.

Thanks,
Robert

Posted by Skrappy on February 12, 2002 12:43 PM

Okay, now I understand the problem better. What is happening is the following.
1. Excel is counting both the start and end times individually.
2. Excel doesnt understand that it is suppoused to flag any values between the start and end; it is just counting the start and end times, so that if you have data that lasts longer than a minute, then it will skip the interior periods.

This is not as simple as I originally thought. I will try to think about an easy solution. Sorry that the histogram didnt solve your problem.
A