ignoring zero values in excel charts

Discussion in 'Microsoft Excel Charting' started by Richard, Sep 12, 2003.

  1. Richard

    Richard Guest

    I am trying to create an x/y scatter plot from an excel
    sheet where the cells in the data range contain formulae
    which sometimes return a zero value - when this occurs I
    want the chart to ignore the value as though the cell was
    empty but I am having no luck in doing this. It would
    also be nice if the LINEST function could be made to
    ignore zero values as well.
     
    Richard, Sep 12, 2003
    #1
    1. Advertisements

  2. Richard

    Joe Guest

    Having the same problem. It's okay if there is absolutely
    nothing in the cell the chart is pulling from, but if the
    actual value is zero, it dips down and looks very
    distracting.
    I have tried various different ways to get around but none
    have been successful as of yet. The way I am currently
    trying is I want to put a user-defined formula in the
    source data values that will return an array. But any
    time I try to enter the function it tells me I am doing it
    wrong...not that it doesn't allow you to put function in
    charts.
    Don't know if this makes sense or not, but if it does in
    any way, let me know.
    >-----Original Message-----
    >I am trying to create an x/y scatter plot from an excel
    >sheet where the cells in the data range contain formulae
    >which sometimes return a zero value - when this occurs I
    >want the chart to ignore the value as though the cell was
    >empty but I am having no luck in doing this. It would
    >also be nice if the LINEST function could be made to
    >ignore zero values as well.
    >.
    >
     
    Joe, Sep 12, 2003
    #2
    1. Advertisements

  3. Richard

    Joe Guest

    Jerry, I appreciate that! All I needed to know was that
    Graphs will ignore #N/A values. Never knew that before.
    Thanks for the tip!

    Joe B.

    >-----Original Message-----
    >No function will ignore zero values. If you are trying

    to do linear
    >regression, you could use the following array formulas

    (assuming x-data
    >is in A1:A20 and y-data in B1:B20)
    >
    >=SLOPE(IF(B1:B20=0,"",B1:B20),IF(A1:A20=0,"",A1:A20))
    >=INTERCEPT(IF(B1:B20=0,"",B1:B20),IF(A1:A20=0,"",A1:A20))
    >
    >These must be array entered (Ctrl-Shift-Enter), and the

    trick cannot be
    >used with LINEST, since LINEST requres a value in every

    referenced cell.
    >
    >Graphs will ignore #N/A values, so you could change each

    cell formula to
    >something like
    >
    >=IF(formula=0,#N/A,formula)
    >
    >for your graph, and modify the preceding SLOPE and

    INTERCEPT formulas to
    >
    >=SLOPE(IF(ISNA(B1:B20),"",B1:B20),IF(ISNA

    (A1:A20),"",A1:A20))
    >=INTERCEPT(IF(ISNA(B1:B20),"",B1:B20),IF(ISNA

    (A1:A20),"",A1:A20))
    >
    >Jerry
    >
    >Richard wrote:
    >
    >> I am trying to create an x/y scatter plot from an excel
    >> sheet where the cells in the data range contain

    formulae
    >> which sometimes return a zero value - when this occurs

    I
    >> want the chart to ignore the value as though the cell

    was
    >> empty but I am having no luck in doing this. It would
    >> also be nice if the LINEST function could be made to
    >> ignore zero values as well.

    >
    >.
    >
     
    Joe, Sep 12, 2003
    #3
  4. Richard

    Jon Peltier Guest

    Tushar Mehta has a routine that blanks out these N/A values, which
    aren't plotted at the ends of a series, but are interpolated over
    between valid points. I forget the precise name, but it has the words
    Chart and N/A in it. Look at the list on the left side of his web site
    (http://tushar-mehta.com).

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    http://www.geocities.com/jonpeltier/Excel/index.html
    _______

    Whirlwind wrote:
    > I'm having a similar problem, except that one of my series
    > should not be graphed for observations that are the same
    > as another series. I put an #N/A in those cells and they
    > are not graphed. The problem is if some values show up,
    > followed by some #N/As, and then some other values show up
    > in a later observation, Excel interpolates between the
    > observations that are not #N/A.
    >
    > Any ideas? Thanks,
    >
    > JR
    >
    >>Graphs will ignore #N/A values, so you could change each

    >
    > cell formula to
    >
    >>something like
    >>
    >>=IF(formula=0,#N/A,formula)
    >>
    >>Jerry
    >>
    >>Richard wrote:
    >>
    >>
    >>>I am trying to create an x/y scatter plot from an excel
    >>>sheet where the cells in the data range contain

    >>

    > formulae
    >
    >>>which sometimes return a zero value - when this occurs

    >>

    > I
    >
    >>>want the chart to ignore the value as though the cell

    >>

    > was
    >
    >>>empty but I am having no luck in doing this. It would
    >>>also be nice if the LINEST function could be made to
    >>>ignore zero values as well.

    >>

    >
     
    Jon Peltier, Sep 24, 2003
    #4
  5. Richard

    thepcer

    Joined:
    Jul 26, 2010
    Messages:
    30
    Likes Received:
    0
    I found I much easier way!

    Plot a new series (hide if you like) with this example command:

    if(iserror(value(A1)),na(),A1)

     
    thepcer, Jun 3, 2011
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. CiaraG

    Omitting zero values from pie charts

    CiaraG, Aug 7, 2003, in forum: Microsoft Excel Charting
    Replies:
    4
    Views:
    809
    Jon Peltier
    Aug 7, 2003
  2. Patty

    zero values in charts

    Patty, Oct 13, 2003, in forum: Microsoft Excel Charting
    Replies:
    4
    Views:
    248
    Patty
    Oct 21, 2003
  3. Guest

    Ignoring zero data in charts

    Guest, Apr 21, 2006, in forum: Microsoft Excel Charting
    Replies:
    2
    Views:
    657
    Guest
    Apr 24, 2006
  4. Guest

    Excel 2002 Zero Values in Charts

    Guest, Aug 18, 2006, in forum: Microsoft Excel Charting
    Replies:
    2
    Views:
    340
    Guest
    Nov 25, 2006
  5. Tony O

    Excel 2007 charts: want to avoid zero values

    Tony O, May 5, 2012, in forum: Microsoft Excel Charting
    Replies:
    0
    Views:
    609
    Tony O
    May 5, 2012
Loading...

Share This Page