Time graphs

[guerilla]

Hi,

How do I plot data along a time value (x axis) if the time is
continuous? In other words, if a data value is "3" for five seconds
then a line will be drawn on the graph and then when the value changes
to "2" for two seconds, the line steps down to the "2" value and plots
for two seconds until the next value change? I basically want a graph
which has time along the bottom (x axis) and a value of 1, 2 or 3 on
the left (y axis), so as the value changes the line is plotted along
the time axis.

Hope I've explained myself?
On intend to use a macro to capture the time value of each value
change to create a table which the graph will use.

regards,
Matt

 

[Herbert Seidenberg]

Excel 2007
Step plot climbs in real time:
http://www.mediafire.com/file/irwmlz1jcnn/04_15_09b.xlsm

 

[Ken Johnson]

Hi,

How do I plot data along a time value (x axis) if the time is
continuous?  In other words, if a data value is "3" for five seconds
then a line will be drawn on the graph and then when the value changes
to "2" for two seconds, the line steps down to the "2" value and plots
for two seconds until the next value change?  I basically want a graph
which has time along the bottom (x axis) and a value of 1, 2 or 3 on
the left (y axis), so as the value changes the line is plotted along
the time axis.

Hope I've explained myself?
On intend to use a macro to capture the time value of each value
change to create a table which the graph will use.

regards,
Matt

Hi Matt,

If you data looks like...

(0, 1)
(3, 2)
(7, 3)
(12, 2)
(15, 1)
(22, 3)

where the left value is "time when change occurred" and the right
value is "the new value" out of just 1,2 and 3, then you need to add
points to the data that will result in square steps rather than the
angled steps that the above data produces when used to make an XY
(Scatter) chart.

If the time values start in A2 and the new values in B2 then...

=INDIRECT("A" &INT(ROW(1:1)/2)+2)

and

=INDIRECT("B"&INT(ROW(2:2)/2)+1)

both filled down far enough (11 rows for above example) will add the
data points needed to produce square steps...

(0, 1)<original
(3, 1)<added
(3, 2)<original
(7, 2)<added
(7, 3)<original
(12, 3)<added
(12, 2)<original
(15, 2)<added
(15, 1)<original
(22, 1)<added
(22, 3)<original

Note that the added data points plot the old value at the same time
that the value changed to the new value.

Ken Johnson

 

[Lori]

To create the step chart directly you can try this...

eg Data range: Sht!A2:B7

0 1
3 2
7 3
12 2
15 1
22 3

First create a line or area chart with a time value x-axis.

Then modify the series formula to:
=SERIES(,(Sht!A2:A7,Sht!A2:A7),(Sht!B2,Sht!B2:B6,Sht!B2:B7),1)

[This duplicates the data with the y-values offset by one,
in this way no additional values need to be added.]

 

[Ken Johnson]

To create the step chart directly you can try this...

eg Data range: Sht!A2:B7

0 1
3 2
7 3
12 2
15 1
22 3

First create a line or area chart with a time value x-axis.

Then modify the series formula to:
=SERIES(,(Sht!A2:A7,Sht!A2:A7),(Sht!B2,Sht!B2:B6,Sht!B2:B7),1)

[This duplicates the data with the y-values offset by one,
in this way no additional values need to be added.]

Hi,

How do I plot data along a time value (x axis) if the time is
continuous?  In other words, if a data value is "3" for five seconds
then a line will be drawn on the graph and then when the value changes
to "2" for two seconds, the line steps down to the "2" value and plots
for two seconds until the next value change?  I basically want a graph
which has time along the bottom (x axis) and a value of 1, 2 or 3 on
the left (y axis), so as the value changes the line is plotted along
the time axis.

Hope I've explained myself?
On intend to use a macro to capture the time value of each value
change to create a table which the graph will use.

regards,
Matt

Hi Lori,

I tried that out with an XY (Scatter) chart with data in A2:B7 on
Sheet1 using...

=SERIES(,(Sheet1!$A$2:$A$7,Sheet1!$A$2:$A$7),(Sheet1!$B$2,Sheet1!$B
$2:$B$6,Sheet1!$B$2:$B$7),1)

It results in the correct set of points, however the lines joining
them are all over the place.

Could I be doing something wrong?

Ken Johnson

 

[Lori]

Ken - You need to use this technique with line or area charts only.
Make sure to set the category axis to time scale, you can then
reformat the x-axis eg to display as general number format.
XY charts connect points consecutively, so this method is not appropriate.

For an XY chart you could use instead: =SERIES(,Sht!x,Sht!y,1)
where you define then names to be...

x: =$A$2:$A$7+{0,0}
y: =LOOKUP(ROW($A$2:$A$7)-{1,0},ROW($A$2:$A$7),$B$2:$B$7)

[The sheet name will be added to the name automatically]
This also seems to work with line and area charts too.

HTH, Lori

 

[Herbert Seidenberg]

Excel 2007
More stuff on step graph, scatter plots
and range unions.
http://www.mediafire.com/file/go3d4znvj5k/04_17_09.xlsx

 

[Lori Miller]

For an XY chart you could use instead: =SERIES(,Sht!x,Sht!y,1)

where you define the names to be...

x: =$A$2:$A$7+{0,0}
y: =LOOKUP(ROW($A$2:$A$7)-{1,0},ROW($A$2:$A$7),$B$2:$B$7)

This also seems to work with line and area charts too.

HTH, Lori

This didn't come through in google groups for some reason.
In addtion, if the names refer to a table range, the chart will auto-update.

 

[Ken Johnson]

This didn't come through in google groups for some reason.
In addtion, if the names refer to a table range, the chart will auto-update.

Thanks Lori.
Very interesting technique.

Ken Johnson