If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#12
|
|||
|
|||
Lines in chart when no data is present
Hi,
Well I test 2000 and it worked, but 2002 SP3 did not. -- Thanks, Shane Devenshire "OssieMac" wrote: I'm not sure if I am on the right track with the problem but if I am, it appears to me that you only have to select the chart, select Tools, Options, Chart Tab and set the Plot Empty Cells option. "Jon Peltier" wrote: This is vaguely familiar (everything before yesterday is only vaguely familiar). I just tested Excel 2003 SP2, and the line can be formatted as no line without a problem. I recall that the issue was that instead of making the line segment vanish, what Excel did was draw the line from the previous point to the subsequent point, so the point in the middle was still there but not connected to either adjacent point. I wonder if that was how Excel 2003 came out, and they fixed it (i.e., changed it back) in an SP. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... Hi Jon, I just retested versions of XL from 2000 to 2007: created a Line chart and then tried to format a single point to 'no line'. As of today, in 2002 (SP2?) it still shows the line. I am reasonably sure that was the same behavior with 2003 when it first came out but today the line disappeared for the single point. So, it was probably fixed in some 2003 SP. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Tushar - In which version of Excel did Microsoft make the change? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... That's the way MS designed the charting capability. If you are plotting a XY Scatter chart, check out the add-in at Chart gap for N/A http://www.tushar-mehta.com/excel/so...ity/index.html It used to work with Line charts until MS made a change that messed up how Line charts deal with breaks in the line. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have found the trick to prevent 0's from showing in a chart when the data is blank: If(H2="",NA(),H2/F2) However, the chart line extrapolates from the previous data point to the next datapoint instead of showing a gap. I've looked at Tools-Options-Chart and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)" is selected. The data is being charted on a secondary Y axis if this makes a difference. Please help. I need to have gaps. |
#13
|
|||
|
|||
Lines in chart when no data is present
That only works with real blank cells, not cells in which a formula returns
"" or any other value. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "OssieMac" wrote in message ... I'm not sure if I am on the right track with the problem but if I am, it appears to me that you only have to select the chart, select Tools, Options, Chart Tab and set the Plot Empty Cells option. "Jon Peltier" wrote: This is vaguely familiar (everything before yesterday is only vaguely familiar). I just tested Excel 2003 SP2, and the line can be formatted as no line without a problem. I recall that the issue was that instead of making the line segment vanish, what Excel did was draw the line from the previous point to the subsequent point, so the point in the middle was still there but not connected to either adjacent point. I wonder if that was how Excel 2003 came out, and they fixed it (i.e., changed it back) in an SP. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... Hi Jon, I just retested versions of XL from 2000 to 2007: created a Line chart and then tried to format a single point to 'no line'. As of today, in 2002 (SP2?) it still shows the line. I am reasonably sure that was the same behavior with 2003 when it first came out but today the line disappeared for the single point. So, it was probably fixed in some 2003 SP. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Tushar - In which version of Excel did Microsoft make the change? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... That's the way MS designed the charting capability. If you are plotting a XY Scatter chart, check out the add-in at Chart gap for N/A http://www.tushar-mehta.com/excel/so...ity/index.html It used to work with Line charts until MS made a change that messed up how Line charts deal with breaks in the line. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have found the trick to prevent 0's from showing in a chart when the data is blank: If(H2="",NA(),H2/F2) However, the chart line extrapolates from the previous data point to the next datapoint instead of showing a gap. I've looked at Tools-Options-Chart and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)" is selected. The data is being charted on a secondary Y axis if this makes a difference. Please help. I need to have gaps. |
#14
|
|||
|
|||
Lines in chart when no data is present
I should clarify, I test the idea of selecting the line segment that is
interpolated across the missing point and changed its color to None and it worked in my copy of 2000 but not in 2002. These cells contained =NA(), not a blank. An alternate way to do this is to set the line color to match the background color rather than to set it to None. With this approach there are issues, but they can all be solved. -- Thanks, Shane Devenshire "Jon Peltier" wrote: That only works with real blank cells, not cells in which a formula returns "" or any other value. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "OssieMac" wrote in message ... I'm not sure if I am on the right track with the problem but if I am, it appears to me that you only have to select the chart, select Tools, Options, Chart Tab and set the Plot Empty Cells option. "Jon Peltier" wrote: This is vaguely familiar (everything before yesterday is only vaguely familiar). I just tested Excel 2003 SP2, and the line can be formatted as no line without a problem. I recall that the issue was that instead of making the line segment vanish, what Excel did was draw the line from the previous point to the subsequent point, so the point in the middle was still there but not connected to either adjacent point. I wonder if that was how Excel 2003 came out, and they fixed it (i.e., changed it back) in an SP. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... Hi Jon, I just retested versions of XL from 2000 to 2007: created a Line chart and then tried to format a single point to 'no line'. As of today, in 2002 (SP2?) it still shows the line. I am reasonably sure that was the same behavior with 2003 when it first came out but today the line disappeared for the single point. So, it was probably fixed in some 2003 SP. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Tushar - In which version of Excel did Microsoft make the change? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... That's the way MS designed the charting capability. If you are plotting a XY Scatter chart, check out the add-in at Chart gap for N/A http://www.tushar-mehta.com/excel/so...ity/index.html It used to work with Line charts until MS made a change that messed up how Line charts deal with breaks in the line. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have found the trick to prevent 0's from showing in a chart when the data is blank: If(H2="",NA(),H2/F2) However, the chart line extrapolates from the previous data point to the next datapoint instead of showing a gap. I've looked at Tools-Options-Chart and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)" is selected. The data is being charted on a secondary Y axis if this makes a difference. Please help. I need to have gaps. |
#15
|
|||
|
|||
Lines in chart when no data is present
I know at some point this functionality was broken. However, it's fixed
again by Excel 2003 SP2; you can change a line segment to None, whether it connects two regular points, spans a blank, or spans NA(). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "ShaneDevenshire" wrote in message ... I should clarify, I test the idea of selecting the line segment that is interpolated across the missing point and changed its color to None and it worked in my copy of 2000 but not in 2002. These cells contained =NA(), not a blank. An alternate way to do this is to set the line color to match the background color rather than to set it to None. With this approach there are issues, but they can all be solved. -- Thanks, Shane Devenshire "Jon Peltier" wrote: That only works with real blank cells, not cells in which a formula returns "" or any other value. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "OssieMac" wrote in message ... I'm not sure if I am on the right track with the problem but if I am, it appears to me that you only have to select the chart, select Tools, Options, Chart Tab and set the Plot Empty Cells option. "Jon Peltier" wrote: This is vaguely familiar (everything before yesterday is only vaguely familiar). I just tested Excel 2003 SP2, and the line can be formatted as no line without a problem. I recall that the issue was that instead of making the line segment vanish, what Excel did was draw the line from the previous point to the subsequent point, so the point in the middle was still there but not connected to either adjacent point. I wonder if that was how Excel 2003 came out, and they fixed it (i.e., changed it back) in an SP. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... Hi Jon, I just retested versions of XL from 2000 to 2007: created a Line chart and then tried to format a single point to 'no line'. As of today, in 2002 (SP2?) it still shows the line. I am reasonably sure that was the same behavior with 2003 when it first came out but today the line disappeared for the single point. So, it was probably fixed in some 2003 SP. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Tushar - In which version of Excel did Microsoft make the change? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Tushar Mehta" wrote in message om... That's the way MS designed the charting capability. If you are plotting a XY Scatter chart, check out the add-in at Chart gap for N/A http://www.tushar-mehta.com/excel/so...ity/index.html It used to work with Line charts until MS made a change that messed up how Line charts deal with breaks in the line. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have found the trick to prevent 0's from showing in a chart when the data is blank: If(H2="",NA(),H2/F2) However, the chart line extrapolates from the previous data point to the next datapoint instead of showing a gap. I've looked at Tools-Options-Chart and the Active Chart - Plot Empty Cells as "Not Plotted (leave gaps)" is selected. The data is being charted on a secondary Y axis if this makes a difference. Please help. I need to have gaps. |
|
Thread Tools | |
Display Modes | |
|
|