Impossible? Not plotting NA()s

Discussion in 'Microsoft Excel Charting' started by LiAD, Mar 31, 2010.

  1. LiAD

    LiAD Guest

    Hi,

    I have no luck looking through the JPelltier website or other such sites so
    can some-one please give me a clear indication of how I can change this. My
    question is

    - How can I stop a chart from leaving space for NA() or change an IF formula
    to show something that the chart will completely ignore?

    From C4:p20 I have a results table (with titles in row 3). The table is
    filled using formulas to index through a master sheet. Depending on the
    criteria selected there may be 2 to 200 items in the table. To cope for all
    eventualities the formula has an =if(cell x=â€â€;NA();do something) to keep it
    tidier.

    My chart, (showing results plus norm, upper and lower limit), has the
    following dynamic named ranges set

    Chart labels - =OFFSET(Diagramme!ChartValues;0;-1)
    Chart Values - =OFFSET(Diagramme!$G$3;1;0;COUNTA(Diagramme!$G:$G)-1;1)
    Max - =OFFSET(Diagramme!$H$3;1;0;COUNTA(Diagramme!$H:$H)-1;1)
    Min - =OFFSET(Diagramme!$J$3;1;0;COUNTA(Diagramme!$J:$J)-1;1)
    Norme - =OFFSET(Diagramme!$I$3;1;0;COUNTA(Diagramme!$I:$I)-1;1)

    I need the formula in all 200 rows, but if I have a chart with only 2 data
    points my plot is stuck to the left of the chart, (effectively my x-axis does
    1-200 with data in 1-2). It leaves the space for all the #NAs without
    plotting anything.

    How can I stop a chart from leaving space for NA() or change the formula to
    show something that the chart will completely ignore?

    Thanks
    LiAD
     
    LiAD, Mar 31, 2010
    #1
    1. Advertisements

  2. LiAD

    Luke M Guest

    Since you're already using dynamic ranges, can you change the COUNTA
    function to COUNT, and thus not count the NA's? Since all the NA's are at
    the end of your data, I believe this would work.
    --
    Best Regards,

    Luke M
    "LiAD" <> wrote in message
    news:...
    > Hi,
    >
    > I have no luck looking through the JPelltier website or other such sites
    > so
    > can some-one please give me a clear indication of how I can change this.
    > My
    > question is
    >
    > - How can I stop a chart from leaving space for NA() or change an IF
    > formula
    > to show something that the chart will completely ignore?
    >
    > From C4:p20 I have a results table (with titles in row 3). The table is
    > filled using formulas to index through a master sheet. Depending on the
    > criteria selected there may be 2 to 200 items in the table. To cope for
    > all
    > eventualities the formula has an =if(cell x="";NA();do something) to keep
    > it
    > tidier.
    >
    > My chart, (showing results plus norm, upper and lower limit), has the
    > following dynamic named ranges set
    >
    > Chart labels - =OFFSET(Diagramme!ChartValues;0;-1)
    > Chart Values - =OFFSET(Diagramme!$G$3;1;0;COUNTA(Diagramme!$G:$G)-1;1)
    > Max - =OFFSET(Diagramme!$H$3;1;0;COUNTA(Diagramme!$H:$H)-1;1)
    > Min - =OFFSET(Diagramme!$J$3;1;0;COUNTA(Diagramme!$J:$J)-1;1)
    > Norme - =OFFSET(Diagramme!$I$3;1;0;COUNTA(Diagramme!$I:$I)-1;1)
    >
    > I need the formula in all 200 rows, but if I have a chart with only 2 data
    > points my plot is stuck to the left of the chart, (effectively my x-axis
    > does
    > 1-200 with data in 1-2). It leaves the space for all the #NAs without
    > plotting anything.
    >
    > How can I stop a chart from leaving space for NA() or change the formula
    > to
    > show something that the chart will completely ignore?
    >
    > Thanks
    > LiAD
    >
     
    Luke M, Mar 31, 2010
    #2
    1. Advertisements

  3. LiAD

    LiAD Guest

    Simple
    Perfect
    Lovely

    Thanks

    "Luke M" wrote:

    > Since you're already using dynamic ranges, can you change the COUNTA
    > function to COUNT, and thus not count the NA's? Since all the NA's are at
    > the end of your data, I believe this would work.
    > --
    > Best Regards,
    >
    > Luke M
    > "LiAD" <> wrote in message
    > news:...
    > > Hi,
    > >
    > > I have no luck looking through the JPelltier website or other such sites
    > > so
    > > can some-one please give me a clear indication of how I can change this.
    > > My
    > > question is
    > >
    > > - How can I stop a chart from leaving space for NA() or change an IF
    > > formula
    > > to show something that the chart will completely ignore?
    > >
    > > From C4:p20 I have a results table (with titles in row 3). The table is
    > > filled using formulas to index through a master sheet. Depending on the
    > > criteria selected there may be 2 to 200 items in the table. To cope for
    > > all
    > > eventualities the formula has an =if(cell x="";NA();do something) to keep
    > > it
    > > tidier.
    > >
    > > My chart, (showing results plus norm, upper and lower limit), has the
    > > following dynamic named ranges set
    > >
    > > Chart labels - =OFFSET(Diagramme!ChartValues;0;-1)
    > > Chart Values - =OFFSET(Diagramme!$G$3;1;0;COUNTA(Diagramme!$G:$G)-1;1)
    > > Max - =OFFSET(Diagramme!$H$3;1;0;COUNTA(Diagramme!$H:$H)-1;1)
    > > Min - =OFFSET(Diagramme!$J$3;1;0;COUNTA(Diagramme!$J:$J)-1;1)
    > > Norme - =OFFSET(Diagramme!$I$3;1;0;COUNTA(Diagramme!$I:$I)-1;1)
    > >
    > > I need the formula in all 200 rows, but if I have a chart with only 2 data
    > > points my plot is stuck to the left of the chart, (effectively my x-axis
    > > does
    > > 1-200 with data in 1-2). It leaves the space for all the #NAs without
    > > plotting anything.
    > >
    > > How can I stop a chart from leaving space for NA() or change the formula
    > > to
    > > show something that the chart will completely ignore?
    > >
    > > Thanks
    > > LiAD
    > >

    >
    >
    > .
    >
     
    LiAD, Mar 31, 2010
    #3
    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. Charles Delamain

    Not plotting zero values

    Charles Delamain, Aug 6, 2003, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    682
  2. Annoyed by Excel

    Graph not plotting what it should be plotting

    Annoyed by Excel, Sep 4, 2003, in forum: Microsoft Excel Charting
    Replies:
    2
    Views:
    740
    Jon Peltier
    Sep 5, 2003
  3. richard

    Scatter plotting not working

    richard, Jan 14, 2004, in forum: Microsoft Excel Charting
    Replies:
    3
    Views:
    1,416
    Jon Peltier
    Jan 14, 2004
  4. Guest

    Not plotting blank cells in a chart.

    Guest, Oct 13, 2004, in forum: Microsoft Excel Charting
    Replies:
    1
    Views:
    437
    Tushar Mehta
    Oct 13, 2004
  5. Guest
    Replies:
    1
    Views:
    348
    Debra Dalgleish
    Apr 1, 2005
Loading...

Share This Page