gaps in xy scatter chart
Alan Cocks
formulae, X & Y. The points are joined by lines. However the Y data is not
always available for all the points leaving gaps. The missing points are
now plotted as zeros.
I would like to leave a gap in the plotted line where the data is missing
instead of the zero. Any ideas?
There are going to be many plots on the one chart.
Alan
Andy Pope
If the missing Y values are true empty cells then check the Plot Empty
cells option.
This can be found by selecting the chart, not necessary if it's a chart
sheet, and then from the menus Tools > Options > Chart (tab).
'Plot Empty cells' either
Not plotted (leaves gaps)
Zero
Interpolated
--
Cheers
Andy
Alan Cocks
But I don't seem to be able to get this to work.
Example.
Column B6 to B14 contains 1, 4, 6, 10, 14, (gap), 5, 3, 1,
Column E6 to E14 contains 1, 3, 6, 7, 9, 12, 15, 16, 17.
F6 to F14 contains =IF(B6="","",B6*2) to =IF(B14="","",B14*2)
The X data comes from E6:E14
The Y data comes from F6:F14
This plots the gap as zero even when I set the Tools>options>Chart to "Not
Plotted"
Even when I use formulae in the form of =IF(B11="",,B11*2) this
happens.
Alan
"Andy Pope" <an...@andypope.info> wrote in message
news:OJNn6m38...@TK2MSFTNGP12.phx.gbl...
Alan Cocks
"Andy Pope" <an...@andypope.info> wrote in message
news:OJNn6m38...@TK2MSFTNGP12.phx.gbl...
Andy Pope
Cells are not empty if they contain formula, even if the result of the
formula is empty or NA().
The line and xy scatter chart do not plot the point BUT the line is
interpolated between the points.
One way to get gaps would be to use Tushar Mehta addin to remove N/A
values; it's on his web site at http://tushar-mehta.com.
Andy Pope
I did a little experimenting...
If your formulas output =NA() when there is no value to be displayed the
chart should remove the point but the line will join valid points either
side of it. This code will go through and set the line style to None for
such occurances.
I must stress this has not been tested to destruction.
Also if you have a single data series and a legend the legend displays
the segments (sure this could be of use at some stage!)
Sub RemoveLineGaps()
Dim intSeries As Integer
Dim intPoint As Integer
Dim vntDummy As Variant
On Error GoTo ErrChartDump
With ActiveChart
For intSeries = 1 To .SeriesCollection.Count
For intPoint = 1 To .SeriesCollection(intSeries).Points.Count
vntDummy =
Application.WorksheetFunction.Index(.SeriesCollection(intSeries).Values,
intPoint)
Next
Next
End With
Exit Sub
ErrChartDump:
If Err.Number = 1004 Then
If intPoint + 1 <=
ActiveChart.SeriesCollection(intSeries).Points.Count Then
ActiveChart.SeriesCollection(intSeries).Points(intPoint +
1).Border.LineStyle = xlNone
End If
Resume Next
End If
Exit Sub
End Sub
let me know if it works for you.
Cheers
Alan Cocks
I have got your macro working in a dummy program, just to test it, and it
appears to work well. Have not yet had time to use it in a real situation
as I am busy putting the finishing touches on another project, which, at the
moment is more urgent.
Thanks very much for your help. It is great being able to get help from so
many knowledgeable people, like yourself, for so little effort on my part.
Alan
"Andy Pope" <an...@andypope.info> wrote in message
news:#9IClW#8CHA...@TK2MSFTNGP11.phx.gbl...