Not to hard, if you don't mind a few formulas.
Here is the range A3:C8. I've formatted all times as h:mm:ss and made
sure that is how they are entered.
8:10:00 0:15:30 1
8:20:00 0:10:30 2
8:35:00 0:13:00 1
9:10:00 0:18:30 2
9:25:00 0:09:30 1
10:05:00 0:05:00 1
A: Start of Up time
B: Length of Up time
C: System Number
We'll use column A for X values of an XY Scatter chart, and column B for
the X error bars. For Y we need a formula. In D2 put 1 and in E2 put 2.
In D3 put this formula
=IF($C3=D$2,1,NA())
and in E2 put this formula
=IF($C3=E$2,1.1,NA())
Fill these formulas down to row 8. This puts a 1 in column D if column C
has a 1, and it puts a 1.1 in column E if column C has a 2. So column C
is for system 1 and column D for system 2, and I offset system 2 up by
0.1 so they don't obscure each other.
Select A2:A8 (yes, include the blank cell), then hold down Ctrl while
selecting D2
8. Start the chart wizard and make an XY Scatter chart (a
Line chart will not work). So you have two sets of points. Double click
the first one, click on the X Error Bar tab, click in the Custom + box,
and select B3:B8 with the mouse. Do the same for the other series.
Format the points to be invisible (no lines, no markers) and format the
error bars with distinctive colors, and no cross at the end. For some
people this might be enough, a line where each system was on. But I can
tell you're a demanding engineer who needs more.
So add Y error bars to both of these series, using a constant value of
-1. This gives you a total of half of a step chart. You need formulas to
get the rest, which we'll get by adding two more series.
Column F will be the X values. Put this formula in F3 and fill it down
the column
=A3+B3
Column G is a helper column, which has this formula
{=IF(ISNUMBER(D3),MIN(IF(ISNUMBER(D4
8),A4:A8)),MIN(IF(ISNUMBER(E4:E8),A4:A8)))}
Don't type the curly braces. Hold down Ctrl+Shift while pressing Enter,
and Excel makes it into an array formula, signified by the braces.
Column H is our new X Error Bar values, and H3 has this formula
=IF(G3>F3,G3-F3,0)
Put 1 into I2 and 2 into J2. Put this formula into I3
=IF($C3=I$2,0,NA())
and this into J3
=IF($C3=J$2,0.1,NA())
and fill all formulas down.
Select F2:F8 (including the blank cell), and hold Ctrl while selecting
I2:J8. Choose Copy from the Edit menu, select the chart, and choose
Paste Special from the Edit menu, and paste the data as new series,
categories in first column, names in first row.
Using the techniques described above, apply the range H3:H8 as positive
X error bars to both new series, and apply positive error bars of
constant value 1 to both. Format all your error bars so both series for
system 1 share a color, as do both for system 2.
Phew!
- Jon
