scatter chart with gaps in series (XL 2007)

[Boris]

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.

 

[Ed Ferrero]

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

 

[Jon Peltier]

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

 

[Boris]

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.

 

[Boris]

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.

 

[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.

 

[Ed Ferrero]

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

 

[Boris]

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.