How do you make a chart with right angles only?

  • Thread starter Thread starter Theresa9599
  • Start date Start date
T

Theresa9599

I have a set of data with times and a bunch of 0's and 1's. For example, at
1:30 p.m., I have a 0....2:00 p.m., I have a 1...2:30 p.m. I have a 0, etc.

I have my 0's and 1's set up on the vertical axis and the times along the
horizontal axis.

I want my chart to show horizontal and vertical lines ONLY. IE: I want a
point at "0" at 1:30 p.m., then I want a horizontal line over to 2:00 p.m.,
and then I want a line going straight up to "1", and then I want a horizontal
line over to 2:30 p.m., and then I want a vertical line straight down to "0",
etc.

Currently, I am getting diagonal lines connecting the points.

Is what I am trying to achieve possible, and if so, how?

Thank you!
 
This is a step chart. You need to have duplicate points, to turn

X1 Y1
X2 Y2
X3 Y3
X4 Y4
etc.

to

X1 Y1
X2 Y1
X2 Y2
X3 Y2
X3 Y3
X4 Y3
X4 Y4

- Jon
 
Thank you, that makes sense, and I'm happy that what I'm trying to do is
actually possible!
 
To make a dynamic chart you can use insert > name > define...

eg: X_Values: =Sheet1!$A$1:$A$5 ; Y_Values: =Sheet1!$B$1:$B$5

StepX: =X_Values+{0,0}
StepY: =LOOKUP(ROW(Y_Values)-{1,0},ROW(Y_Values),Y_Values)

Now set the chart formula to: =SERIES(,Sheet1!StepX,Sheet1!StepY,1)
 
Nicely done! I'm not sure why it works since the formulas yield 2D arrays
but it works.
--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu
 
I did see Lori's response. Since I'm kind of new at this, I'm trying to
figure it all out! LOL!

I've been trying to implement John's suggestion, but my graph still is not
looking quite like what I'm going for (when I duplicate the points on the
X-axis, I'm getting just that...two points).

I've also been experimenting with using error bars (found that on another
thread) but that is giving me a headache as well.

Theresa
 
I also keep on forgetting to mention that I am using Excel 2007, if that
makes any difference.
 
The error bar technique on my website works just fine in Excel 2007...almost
fine.

The simple mechanism I remember for Excel 2007 is this. When specifying
custom ranges, always also specify that you want both 'both' plus and minus
error bars. Then, in the custom range dialog box set the one that you don't
want to zero.

So, suppose you have the horizontal error bar values in C2:C5 (and C6 is
empty). Then, to create only plus error bars for the horizontal error bars,
in the error bar dialog box specify 'both' and in the custom range dialog
box, for the plus values select the range C2:C6 and in the minus values box
enter 0.
--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu
 
I'm getting there...

Do I have to use a scatter chart to begin? When I use a scatter chart, I
can't even get close, but then I use a line chart, I can get the Y-Error bars
on there o.k. but not the X-bars (cannot seem to find an option to insert the
X-Error Bars).

Theresa

:
 
The problem with error bars in 2007 is that first it's hard to get to via
the ribbon, then when you select something, it takes a while to modify it to
a useful form.

I got so frustrated I wrote myself a little program to handle error bars for
me. It makes the Excel 2003 error bar experience a little easier, but it
makes error bars much easier in 2007. I've posted the program for anyone to
use:

http://peltiertech.com/WordPress/error-bars-in-excel-2007/

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/>
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______
 
i think it works because the series formula changes ";" to ","
in arrays. A simpler option is to use:

=SERIES(,(Sheet1!A2:A5,Sheet1!A1:A5),(Sheet1!B1:B4,Sheet1!B1:B5),1)

which applies to line and area charts with time scale x-axis, i.e.
dates or integers, but not to XY charts due to the data ordering.
 
Back
Top