Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
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! |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Theresa9599" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
Thank you, that makes sense, and I'm happy that what I'm trying to do is
actually possible! "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Theresa9599" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
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) "Theresa9599" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
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 "Lori Miller" wrote: 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) "Theresa9599" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
I hope you saw Lori Miller's response.
You can also use error bars to make step charts w/o duplicating the data set. See Step chart http://www.tushar-mehta.com/excel/ch...art/index.html -- 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 "Theresa9599" wrote: 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! |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
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 "Tushar Mehta" wrote: I hope you saw Lori Miller's response. You can also use error bars to make step charts w/o duplicating the data set. See Step chart http://www.tushar-mehta.com/excel/ch...art/index.html -- 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 "Theresa9599" wrote: 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! |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
I also keep on forgetting to mention that I am using Excel 2007, if that
makes any difference. |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
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 "Theresa9599" wrote: I also keep on forgetting to mention that I am using Excel 2007, if that makes any difference. |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
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 "Theresa9599" wrote: |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
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/err...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...00906ACNJ.html _______ "Tushar Mehta" wrote in message ... 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 "Theresa9599" wrote: I also keep on forgetting to mention that I am using Excel 2007, if that makes any difference. |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
Use an XY chart. Why do you say it isn't working?
A line chart will not allow horizontal error bars, but an XY chart will. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Theresa9599" wrote in message ... 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 "Theresa9599" wrote: |
#13
Posted to microsoft.public.excel.charting
|
|||
|
|||
How do you make a chart with right angles only?
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. "Tushar Mehta" wrote in message ... 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 "Lori Miller" wrote: 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) "Theresa9599" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i enter angles in exel? | Excel Discussion (Misc queries) | |||
How to create a table which calculates the angles in a Pie Chart? | Charts and Charting in Excel | |||
decimal angles | New Users to Excel | |||
Angles | Charts and Charting in Excel | |||
Cell Formatting for Angles | Excel Worksheet Functions |