Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    London
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Supressing zeros on charts (Excel 2000)

    Does anybody know how to suppress zero values on a line graph, so that the graph does not post a value ?

    When the source cell is blank, the line chart does not post a value (desired outcome).

    BUT, if there is a formula in the source cell, then the chart plots a zero value.

    I have tried FORMATTING the source cell, and IF statements in the formulas to produce blank cells, but it seems that so long as there is any underlying formula in the cell, then the chart still plots the value as a ZERO, not a BLANK.

    Is there an easy solution ?

    Thanks

    David Gazzola, London
    +44 771 570 4815

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Supressing zeros on charts (Excel 2000)

    Use a different formula that returns #N/A instead of a 0 or "" like in:

    =IF(A1,do-this,#N/A)

    Aladin
    Microsoft MVP - Excel

  3. #3
    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: Supressing zeros on charts (Excel 2000)

    Hi,
    Depending on your situation, you may be able to use a defined name as the source data for your chart. If for example, your formulae are in A1:A4 and you only want to plot the ones that show a value, you could define a name as =OFFSET($A$1,0,0,count($A:$A),1). If you called this name PlotArea you could then define your data series as =PlotArea and it should only plot the visible values.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Supressing zeros on charts (Excel 2000)

    To set how Excel deals with missing data, select your chart and choose Tools, Options. In the Options dialog box, click the Chart tab and then select the appropriate option. Your choice will apply to all data series in the selected chart. That means, that you need to have a chart selected before you can select the options.

    You can also represent data with the formula =NA() instead of leaving a cell blank. The chart will use interpolation for data cells that contain this formula, regardless of the setting in the Options dialog box.

Posting Permissions

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