Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Line Chart: Line drops to Bottom if Zero (Excel 2000)

    I have a line chart with some zero values in the middle, for example: 5, 6, 7, 0, 4, 8 .... The line chart shows a steep drop at 0 value. Is there a way to have the line showing a gap rather than dropping to zero, then going up again ?

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Line Chart: Line drops to Bottom if Zero (Excel 2000)

    in the tools/options/chart tab there are options to plot empty cells as zero, interpolated or leave gaps.
    Set this up for your graph workbook, however note carefully that a value of 'zero' is not 'empty'.
    An empty cell is empty, or you could try inserting the value #N/A which will be interpreted similarly.
    To do this use a formula like =if(cell_value=0,#n/a, cell_value)

    HIH

  3. #3
    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: Line Chart: Line drops to Bottom if Zero (Excel 2000)

    The only way to leave a gap is to delete the point (the cell must be empty/blank). You can NOT get a blank as the result of a formula.

    If you use =na() (or put it in a formula), it will not "zero" the point, but will instead be "ignored" so the point before and after it will be connected directly.

    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
  •