Plotting average values against time intervals

[PierreL]

I record average (also max/min) data (Heart rate, Climb rate, descent rate)
against either duration or distance. The graph in its simplest form should
look like a series of horizontal bars showing the average, the length of the
bar being duration or distance covered.
Stock charts show the this min,avge,max value but the width of the vertical
bar his constant and does not vary with the duration or distance.

How do I create this?
Thanks, Pierre

 

[Del Cotter]

The graph in its simplest form should look like a series of horizontal
bars showing the average, the length of the bar being duration or
distance covered. Stock charts show the min,avge,max value but the
width of the vertical bar is constant and does not vary with the
duration or distance.

It sounds as though you want a stepped Line chart, or a stepped stacked
Area chart. You can build this using the "Time-scale" X-axis type,
making sure that time T, the time of switch-over from one average to the
next, is represented *twice* in the data; once for the previous average
and once for the new average. This turns the sloping lines of a line
chart into a stepped pattern of horizontal and vertical lines.

You can choose the time-scale X-axis by selecting "Chart Options" in the
chart menu, and clicking the radio button marked "Category (X)
axis/Time-scale". Make sure that your times are in a numerical format,
and are positive numbers between 0 and about 2.9 million, like allowed
Excel dates. (actual date and time format should be fine)

Blank rows in the data table will eliminate the vertical lines if you
don't want them, leaving only the horizontals.

An alternative method would be to use X-error bars, of width set to
match the times. Each switch-over time is then represented only once in
the data table, and you will have a second column for duration,
calculated from the differences in times, from which the X-error bar
widths are taken.

 

[PierreL]

Thanks Del,
I had figured before out this double entry repetition but was not getting
what I wanted.
Reason probably is that I cannot check the radio button as you recommend. It
remains desparately grey, out of reach.

I have not spotted either where the X error bars locate. I have Excel 2003
Pierre

 

[PierreL]

I have made progress, I was stuck on XY charts. Now with normal lines I have
the radio button turned on but all my values are on a vertical line. Looks
like the scale does not want to extand to a few hours, only days,
months,years.!
Original datapoints read, say, 02:03:42 and I changed them to look like
0.085903.
Is this still the wrong format?

 

[Del Cotter]

I have made progress, I was stuck on XY charts. Now with normal lines I have
the radio button turned on but all my values are on a vertical line. Looks
like the scale does not want to extand to a few hours, only days,
months,years.!

I see you're right, it only works for whole dates and not for times.
What a crock. Sorry I misled you. When I said that Time-scale worked on
any positive number up to 2.9 million, I was wrong; it evidently only
works with any positive *integer* up to 2.9 million.

(this also means you won't be able to have a stepped stacked area chart;
that's a technique that only works with Time-scale)

Well, if you go back to XY as you were originally, you won't need the
Time-scale format after all, so forget I said "Line chart". I just
thought a Line chart with timescale would look nicer with your times,
but if the Time-scale format won't work with your times at all, there's
no advantage. Stick with XY like you had at first. You can still format
the X axis with time format.

Here's a sample of my layout

Time Average
4:21:01 0.84
5:35:28 0.84

5:35:28 0.19
6:55:12 0.19

6:55:12 0.80
8:01:05 0.80

8:01:05 0.10
9:00:14 0.10

This layout gives you horizontal lines and no verticals, with a Scatter
(XY) type, formatted as lines and no symbols (or lines and symbols, but
not symbols and no lines). I've tested it this time, and it works. If
you leave out the blank lines, the graph will have vertical steps
between the horizontals.

You can find the X error bars in the "Format Series..X Error Bars" tab,
if you want to try that method. The Custom box at the bottom lets you
enter a range for horizontal lines.

 

[PierreL]

Thanks Del, works very nicely.
I was just working on a go around by mathematically transforming hours into
days to still use the days timescale and use a text box borrowed
(printscreen) from a graph with the correct hours timescale to cut and paste
this timescale image. But that is everytime a manual cut/paste and was not
going to be very elegant.

 

[Del Cotter]

Thanks Del, works very nicely.

You're welcome, and thanks for the feedback.

 

[Jon Peltier]

You could fudge the scale by, for example, multiplying the times by 1000,
and not using the default date formatted axis labels. This would allow step
charts based on times instead of whole dates. I show this with my combo
XY-Area chart:

http://peltiertech.com/Excel/Charts/XYAreaChart.html

- Jon

 

[PierreL]

Thanks Jon, as usual your solutions take us an extra mile.
I had thought of "fudging the scale" and started doing it when Del offered
this solution.
However, I have noted your solution for another application which I had left