excel timeline graph for many units with color

[mechi]

Hi!

I created (using VBScript) the Excel file below from a csv file.
This is data for one day from one unit.
The numbers in the missing column (dif = 1) are how many seconds were missed.
The numbers is the OK column (dif = 0) are how many seconds were recorded
correctly.
The fromTime/toTime columns are correct - just the times are not given in
seconds. (I had to convert them to numeric with 5 decimal places in order to
figure out actual seconds.)

Eventually I'll have to graph about 300 units over an entire month. We'd
want to see visually if there were certain times when many units
misfunctioned, etc. I'd like some advice about what kind of graphs I should
use. I've been checking on the Internet and am quite overwhelmed with all
types of graphs.

I need a timeline from the beginning of the month until the end. OK
recordings should be green colord, missed recordings of less than 1 minute
should be yellow, and more than a minute (>60) should be red.

I need as many graphs on one page as possible.

Thanks for any ideas,

Mechi


fromTime toTime dif missing OK
05/01/2008 00:00 05/01/2008 11:03 0 39837
05/01/2008 11:03 05/01/2008 11:04 1 16
05/01/2008 11:04 05/01/2008 11:14 0 627
05/01/2008 11:14 05/01/2008 11:15 1 33
05/01/2008 11:15 05/01/2008 11:59 0 2699
05/01/2008 11:59 05/01/2008 12:00 1 7
05/01/2008 12:00 05/01/2008 12:14 0 903
05/01/2008 12:14 05/01/2008 12:15 1 8
05/01/2008 12:15 05/01/2008 12:29 0 897
05/01/2008 12:29 05/01/2008 12:30 1 12
05/01/2008 12:30 05/01/2008 12:44 0 901
05/01/2008 12:44 05/01/2008 12:45 1 13
05/01/2008 12:45 05/01/2008 17:49 0 18280
05/01/2008 17:49 05/01/2008 18:00 1 621
05/01/2008 18:00 05/01/2008 18:10 0 619
05/01/2008 18:10 05/01/2008 18:29 1 1121
05/01/2008 18:29 05/01/2008 18:29 0 42
05/01/2008 18:29 05/01/2008 18:30 1 18
05/01/2008 18:30 05/02/2008 01:30 0 25216







--------------------------------------------------------------------------------

 

[mechif]

I guess my message was too wordy.
Anyhow, I've looked at the StepChart timeline and the StackedBar -
with different widths. I need a combination, I think, but I have no
idea how to go about programming it. The data can be set up in any
way, since I'm using VBScript.

May 1st 15th 31st

------------||||||||||||||||||--------------||-------||=||----||
=|||||||=|||||-----

The x-axis is a time line. The dashes, equal signs and lines
represent recording OK, missing <60, missing >60
I'll use colors instead of the dash/equal/line.
My graph will have to be viewed at 20% since the numbers are so
different (7 and 39837) and if someone wants details, can see it
enlarged.
Any ideas?
Thanks!

 

[Del Cotter]

I guess my message was too wordy.
Anyhow, I've looked at the StepChart timeline and the StackedBar -
with different widths. I need a combination, I think, but I have no
idea how to go about programming it.

I would use an XY (Scatter) Chart with x error bars. The details are
more awkward to explain than to show, so here's an example:

http://www.branta.demon.co.uk/excel/timeline.xls

Condition your data like this:

fromTime Unit OK miss<60 miss>60 starttick
05/01/2008 12:30 0001 0:15:01
05/01/2008 12:44 0001 0:00:13
05/01/2008 12:45 0001 5:04:40
05/01/2008 17:49 0001 0:10:21 0:00:00
05/01/2008 18:00 0001 0:10:19
05/01/2008 18:10 0001 0:18:41 0:00:00
05/01/2008 18:29 0001 0:00:42
05/01/2008 18:29 0001 0:00:18
05/01/2008 18:30 0001 7:00:16
05/01/2008 00:00 0002 11:03:57
05/01/2008 11:03 0002 0:00:16
05/01/2008 11:04 0002 0:10:27
05/01/2008 11:14 0002 0:00:33

Get rid of your toTime because it's not of interest if all the durations
are in the OK and miss columns.

Give your units actual numbers, and use them as the y value. Make three
series that all use the same two columns as x and y.

Now give them three sets of x right error bars, one each. Green for OK,
orange for small miss, and red for long miss.

Use the "starttick" dummy zero as a x left error bar, so your long
misses have bars at both ends.

 

[mechi]

Thanks - I'm going to definitely try this!!

I would use an XY (Scatter) Chart with x error bars. The details are
more awkward to explain than to show, so here's an example:

http://www.branta.demon.co.uk/excel/timeline.xls

Condition your data like this:

fromTime Unit OK miss<60 miss>60 starttick
05/01/2008 12:30 0001 0:15:01
05/01/2008 12:44 0001 0:00:13
05/01/2008 12:45 0001 5:04:40
05/01/2008 17:49 0001 0:10:21 0:00:00
05/01/2008 18:00 0001 0:10:19
05/01/2008 18:10 0001 0:18:41 0:00:00
05/01/2008 18:29 0001 0:00:42
05/01/2008 18:29 0001 0:00:18
05/01/2008 18:30 0001 7:00:16
05/01/2008 00:00 0002 11:03:57
05/01/2008 11:03 0002 0:00:16
05/01/2008 11:04 0002 0:10:27
05/01/2008 11:14 0002 0:00:33

Get rid of your toTime because it's not of interest if all the durations
are in the OK and miss columns.

Give your units actual numbers, and use them as the y value. Make three
series that all use the same two columns as x and y.

Now give them three sets of x right error bars, one each. Green for OK,
orange for small miss, and red for long miss.

Use the "starttick" dummy zero as a x left error bar, so your long
misses have bars at both ends.