Results 1 to 8 of 8
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    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.

    AW
    Andrew

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    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 Sub
    Andrew

  6. #6
    2 Star Lounger
    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

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    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 again
    Andrew

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •