Time graphs

  • Thread starter Thread starter guerilla
  • Start date Start date
G

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
 
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
Click to expand...

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
 
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.]
 
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.]

guerilla said:
Hi,
Click to expand...
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.
Click to expand...
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.
Click to expand...
regards,
Matt
Click to expand...
Click to expand...

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
 
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
 
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
Click to expand...

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.
 
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.
Click to expand...

Thanks Lori.
Very interesting technique.

Ken Johnson
 
Back
Top