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 |
#1
|
|||
|
|||
scatter chart with gaps in series (XL 2007)
Hi, I am plotting large blocks of data on a scatter plot. I want to change my
data and update though charts automatically so I use a series of if statements to generate the data I will plot. My data should have numerous blanks so I can make my if statement give me a blank string, or zero value , or some other value, or NA() when my test fails and otherwise give me the result I want to plot. Fine. Null strings are plotted as 0, NA()’s are not plotted. But the parts of the plot ranges containing data (where the data passed my test) are connected by straight lines (representing the parts of my plot ranges where the test failed and the output is NA()) when I wanted to leave these segments as gaps in the data line. Is there any way of telling Excel not to join the points together ie plot only my values and not plot the joining lines where there are NA()'s (that is, mimic having true blank cells)? I have resorted to writing a simple bit of code to give me the data I need but this is not the elegant solution I was hoping for... Many thanks and best wishes, Boris. |
#2
|
|||
|
|||
scatter chart with gaps in series (XL 2007)
Hi Boris,
I do not have 2007 installed at the moment, so I cannot test this, but try changing the chart type to 3D Line. Then set the elevation, perspective, and rotation to zero (in properties). Should look like a line chart and leave gaps for NA() values, if I remember correctly. Ed Ferrero www.edferrero.com |
#3
|
|||
|
|||
scatter chart with gaps in series (XL 2007)
Boris -
There is no worksheet function that mimics a blank cell. NA() is the best you can do if all you need is to skip a plotted point, but as you've discovered, the connecting line is drawn across the skipped point. Apparent blanks ("") plot a point at zero, which is even worse. You could try Ed's suggestion, though using a 3D line makes me shudder. The 3D line would impose all limitations of 3D charts (no markers, no combo charts, etc.) and of line charts (non-numeric X scale). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Boris" wrote in message ... Hi, I am plotting large blocks of data on a scatter plot. I want to change my data and update though charts automatically so I use a series of if statements to generate the data I will plot. My data should have numerous blanks so I can make my if statement give me a blank string, or zero value , or some other value, or NA() when my test fails and otherwise give me the result I want to plot. Fine. Null strings are plotted as 0, NA()'s are not plotted. But the parts of the plot ranges containing data (where the data passed my test) are connected by straight lines (representing the parts of my plot ranges where the test failed and the output is NA()) when I wanted to leave these segments as gaps in the data line. Is there any way of telling Excel not to join the points together ie plot only my values and not plot the joining lines where there are NA()'s (that is, mimic having true blank cells)? I have resorted to writing a simple bit of code to give me the data I need but this is not the elegant solution I was hoping for... Many thanks and best wishes, Boris. |
#4
|
|||
|
|||
scatter chart with gaps in series (XL 2007)
Hi Ed,
Thank you for the tip. I will certainly try it, though, for reasons as pointed out by Jon, a 3D line graph is probably not appropriate for me for my current chart... Pity this is not just an option (such as there is for dealing with empty cells).... Thanks again for your very helpful suggestion. Best wishes, Boris. "Ed Ferrero" wrote: Hi Boris, I do not have 2007 installed at the moment, so I cannot test this, but try changing the chart type to 3D Line. Then set the elevation, perspective, and rotation to zero (in properties). Should look like a line chart and leave gaps for NA() values, if I remember correctly. Ed Ferrero www.edferrero.com |
#5
|
|||
|
|||
scatter chart with gaps in series (XL 2007)
Dear Jon,
Thanks for your helpful (as always) reply. I have used the NA() "solution" for many a year now and it has served me OK but, unfortunately not for the current chart... It is a real pity that there is not a simple option to treat NA() values like blanks... but that is hardly the most important of the missing features in Excel 2007 charts. Thanks again and very best wishes, Boris. "Jon Peltier" wrote: Boris - There is no worksheet function that mimics a blank cell. NA() is the best you can do if all you need is to skip a plotted point, but as you've discovered, the connecting line is drawn across the skipped point. Apparent blanks ("") plot a point at zero, which is even worse. You could try Ed's suggestion, though using a 3D line makes me shudder. The 3D line would impose all limitations of 3D charts (no markers, no combo charts, etc.) and of line charts (non-numeric X scale). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Boris" wrote in message ... Hi, I am plotting large blocks of data on a scatter plot. I want to change my data and update though charts automatically so I use a series of if statements to generate the data I will plot. My data should have numerous blanks so I can make my if statement give me a blank string, or zero value , or some other value, or NA() when my test fails and otherwise give me the result I want to plot. Fine. Null strings are plotted as 0, NA()'s are not plotted. But the parts of the plot ranges containing data (where the data passed my test) are connected by straight lines (representing the parts of my plot ranges where the test failed and the output is NA()) when I wanted to leave these segments as gaps in the data line. Is there any way of telling Excel not to join the points together ie plot only my values and not plot the joining lines where there are NA()'s (that is, mimic having true blank cells)? I have resorted to writing a simple bit of code to give me the data I need but this is not the elegant solution I was hoping for... Many thanks and best wishes, Boris. |
#6
|
|||
|
|||
scatter chart with gaps in series (XL 2007)
Hi Ed,
Just to say your trick (sort of) works in Excel 2007 too. Only the graphs are then limited to just 4000 datapoints per series (no good for me) and the charting engine also screws up the sapcing eg between the title , x-axis title and labes, and the chart area. Also, you lose any secondary y axis and the whole workbook becomes incredibly slow but... in principle it works. Best wishes, and many thanks, Boris. "Ed Ferrero" wrote: Hi Boris, I do not have 2007 installed at the moment, so I cannot test this, but try changing the chart type to 3D Line. Then set the elevation, perspective, and rotation to zero (in properties). Should look like a line chart and leave gaps for NA() values, if I remember correctly. Ed Ferrero www.edferrero.com |
#7
|
|||
|
|||
scatter chart with gaps in series (XL 2007)
Hi Boris,
Hi Ed, Just to say your trick (sort of) works in Excel 2007 too. Only the graphs are then limited to just 4000 datapoints per series (no good for me) and the charting engine also screws up the sapcing eg between the title , x-axis title and labes, and the chart area. Also, you lose any secondary y axis and the whole workbook becomes incredibly slow but... in principle it works. Best wishes, and many thanks, Boris. More than 4000 data points - I did not test with a large data sample. Ok you could try to copy the series range as values, then replace the #N/A values with blanks, then chart that. Here is a little VBA code that will do it for you - it copies the original data to the next column so that you do not lose the original formulas. Sub MakeChartable() Dim rngInput As Range Application.ScreenUpdating = False ' enter series input range here ' assume that series is in range D44000 Set rngInput = Worksheets(1).Range("D44000") ' copy the input range, paste values in next column ' and replace the #N/A values with blanks rngInput.Copy With rngInput.Offset(0, 1) .PasteSpecial xlPasteValues .Replace What:="#N/A", Replacement:="" End With Set rngInput = Nothing Application.ScreenUpdating = True End Sub Ed Ferrero www.edferrero.com |
#8
|
|||
|
|||
scatter chart with gaps in series (XL 2007)
Dear Ed,
Many thanks. That is indeed what I ended up doing but, given the size of the data, I would of course prefer not to have to do so. But many thanks for the code (I didn't try writing one myself so this is very helpful). Best wishes, Boris. "Ed Ferrero" wrote: Hi Boris, Hi Ed, Just to say your trick (sort of) works in Excel 2007 too. Only the graphs are then limited to just 4000 datapoints per series (no good for me) and the charting engine also screws up the sapcing eg between the title , x-axis title and labes, and the chart area. Also, you lose any secondary y axis and the whole workbook becomes incredibly slow but... in principle it works. Best wishes, and many thanks, Boris. More than 4000 data points - I did not test with a large data sample. Ok you could try to copy the series range as values, then replace the #N/A values with blanks, then chart that. Here is a little VBA code that will do it for you - it copies the original data to the next column so that you do not lose the original formulas. Sub MakeChartable() Dim rngInput As Range Application.ScreenUpdating = False ' enter series input range here ' assume that series is in range D44000 Set rngInput = Worksheets(1).Range("D44000") ' copy the input range, paste values in next column ' and replace the #N/A values with blanks rngInput.Copy With rngInput.Offset(0, 1) .PasteSpecial xlPasteValues .Replace What:="#N/A", Replacement:="" End With Set rngInput = Nothing Application.ScreenUpdating = True End Sub Ed Ferrero www.edferrero.com |
Thread Tools | |
Display Modes | |
|
|