Results 1 to 8 of 8

20060302, 19:56 #1
 Join Date
 Apr 2001
 Location
 Cambridge, UK
 Posts
 1,020
 Thanks
 0
 Thanked 3 Times in 3 Posts
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.
Many thanks if there is a solution.Andrew

20060302, 20:11 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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]
Steve

20060302, 20:33 #3
 Join Date
 Apr 2001
 Location
 Cambridge, UK
 Posts
 1,020
 Thanks
 0
 Thanked 3 Times in 3 Posts
Re: Stop excel plotting zero values? (Excel 2003)
Yes, that's what I suspected.
I'll probably leave it.
Or have a routine to change line colours.
Then I don't have to edit out the formulas and then edit them back in.
I'll see how it goes.
AWAndrew

20060302, 22:11 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,298
 Thanks
 3
 Thanked 203 Times in 187 Posts
Re: Stop excel plotting zero values? (Excel 2003)
I you use the NA() route, you should be able to adjust the totals formulae to ignore the errors. You can also use conditional formatting to hide the errors if necessary.
HTH.Regards,
Rory
Microsoft MVP  Excel

20060303, 09:24 #5
 Join Date
 Apr 2001
 Location
 Cambridge, UK
 Posts
 1,020
 Thanks
 0
 Thanked 3 Times in 3 Posts
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
End SubAndrew

20060306, 10:41 #6
 Join Date
 Feb 2001
 Location
 Shetland Isles, Shetland, Scotland
 Posts
 154
 Thanks
 3
 Thanked 1 Time in 1 Post
Re: Stop excel plotting zero values? (Excel 2003)
I'd a similar problem which I solved with the attached workaround, hope this helps.
Jim
Shetland Isles

20060306, 18:15 #7
 Join Date
 Apr 2001
 Location
 Cambridge, UK
 Posts
 1,020
 Thanks
 0
 Thanked 3 Times in 3 Posts
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.
Thanks againAndrew

20060306, 18:40 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Stop excel plotting zero values? (Excel 2003)
If you put the numbers 0  5 in C1: H1
You can change the formula in C2 to:
=IF($B2=0,#N/A,IF(COUNTIF($B$2:$B2,0)=C$1,$B2,#N/A))
Then copy this to c2:H19. It does not require separate formulas for each column. adding new "ranges" is as easy as copying columns and adding to the header.
Steve