Stop excel plotting zero values? (Excel 2003)

Not sure I can do this.
If you have a set of data and some cells are not entered, then when excel plots the chart it can be set to not plot these points.
This is fine.
If however, the values lie in a calculated column where the results are added up, then the system returns 0.
I don't want this plotted because it dips to zero, nor do I want it interpolated because there must be NO line.
I want the line NOT to join.
I tried using an if to return "", but it still plotted to zero.
I could use a column chart but a line is trhe one requested.
Is there a solution to this, or is it a no no, which I am begining to suspect.
I could write some fancy vba to change the line color of the section if the value is zero
but that seems like overkill.
Someone said you can use na(), but it doesn't work for me, and anyway it mucks up the column totals.

Re: Stop excel plotting zero values? (Excel 2003)

The only way to not plot points (ie leave a gap in the line) is to have the cell literally empty.

#NA error can be used if you want the line interpolated (no point plotted, but no gap in the line)

Text (including a null string) or any other error in a cell will plot as zero on the chart.

If you are willing to go the VB route, i would suggest using the formulas to put errors in the cells (instead of zeroes) and then have the routine clear the cells with errors. [You will have to have a routine first fill in the "blanks" with the formulas, then clear them, whenever you update the data and run the routine]

Re: Stop excel plotting zero values? (Excel 2003)

I could have used the NA route, but have a chart solution instead.
The code below is a sample of the part of the solution.
I have triggered mine from the Sheets change event
only if the target cell falls in a specific range.

For what it is worth to anyone else, code sample is below.

Andrew

Sub CheckLines()

Dim ocht As ChartObject
Dim cht As Chart
Dim oSec As Series
Dim oP As Point, oL As DataLabel '(oL Not used here)
Dim intS As Integer, intP As Integer, intMax As Integer
Dim intColourLoop As Integer
Dim dblT As Double, dblE As Double, dblD As Double 'Used to time loop
Dim varData(), varValue, dblValue As Double 'Used to store value array

'Obviously you may need to alter this to suit your data
'Select correct chart
ActiveSheet.ChartObjects(1).Select
Set cht = ActiveChart

'Loop through all the series collections on the chart
For intS = 1 To cht.SeriesCollection.Count
'Put labels on. You can disable this bit if you don't want them
Set oSec = cht.SeriesCollection(intS)
'Set up the label font and size (optional)
oSec.HasDataLabels = True
oSec.DataLabels.Font.Size = 8
oSec.DataLabels.Font.Name = "Arial"
'Add lines to all sections so that they can be removed as needed
oSec.Border.LineStyle = xlContinuous
'You don't need this next bit, it is just an annoying loop that flashes colour
'------------------------------------------------------------------------
If intS = 1 Then
For intColourLoop = 1 To 4
Randomize
oSec.Border.ColorIndex = Int(Rnd() * 55) + 1
dblT = Time
dblE = Time + 0.00001
Do Until Time >= dblE
Loop
DoEvents
Next
End If
'------------------------------------------------------------------------
'Get points in the series
intMax = oSec.Points.Count
'Dimension array for points
ReDim varData(intMax)
'Assign data values to the array
varData = oSec.Values
For intP = 1 To intMax
On Error GoTo skipnovalueerror
'Get value of point (array is starting at 1) 'Not consistent, but very MS
varValue = varData(intP)
'Check for Numeric
If IsNumeric(varValue) Then
dblValue = varValue
End If
'Check which point because lines of end points MUST be handled differently
'Check the value of the point
If dblValue = 0 Then
'If point has NO value then we must clear line and NOT show marker
If intP > 1 And intP < intMax Then
'Centre Points so must clear line BOTH sides (may have been done but need to be safe)
oSec.Points(intP).Border.LineStyle = xlNone
oSec.Points(intP + 1).Border.LineStyle = xlNone
ElseIf intP = intMax Then
'End Point so clear line from this point only
oSec.Points(intP).Border.LineStyle = xlNone
ElseIf intP = 1 Then
'First Point so clear line to NEXT point only
oSec.Points(intP + 1).Border.LineStyle = xlNone
End If
'Now handle the Marker and label at the point
oSec.Points(intP).MarkerStyle = xlNone
If oSec.Points(intP).HasDataLabel = True Then
oSec.Points(intP).DataLabel.Delete
End If
End If
'End If
skipnovalueerror:
On Error GoTo 0
Next
'Clear away the labels we added at the start enable if you want them to dissappear
'oSec.HasDataLabels = False
Next

Re: Stop excel plotting zero values? (Excel 2003)

Thanks Jim.
I have filed this one away, but it does not quite solve what I was trying to do.
In my problem I have a set of Values to be plotted as a line.

Say, 2,4,6,3,0,5,0,6,7,2

I wanted to be able to plot a continuoes line apart from the zero values.
So, lines from 1 -4 - 6 - 3 then No lines but plotting starts again at 5 with just the 1 point
then again with point 6 - 7 - 2, but NO dips to zero.
Even substituting the #N/A as per your example it seemd to plot the line continuously.

I have the vba solution that works fine, since the sheet is only updated once a week, it is fine.
I'll have a play with your solution to see if i can get it to work the way I want it to.