PC Review


Reply
Thread Tools Rate Thread

Impossible? Not plotting NA()s

 
 
LiAD
Guest
Posts: n/a
 
      31st Mar 2010
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

 
Reply With Quote
 
 
 
 
Luke M
Guest
Posts: n/a
 
      31st Mar 2010
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>



 
Reply With Quote
 
 
 
 
LiAD
Guest
Posts: n/a
 
      31st Mar 2010
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >

>
>
> .
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Graph not plotting correctly if source not selected Frank Microsoft Excel Programming 5 7th Jun 2010 05:49 PM
Scatter plotting not working richard Microsoft Excel Charting 3 14th Jan 2004 10:55 PM
Graph not plotting what it should be plotting Annoyed by Excel Microsoft Excel Charting 2 5th Sep 2003 09:53 PM
Not plotting missing data as zeros on chart Alistair Eberst Microsoft Excel Worksheet Functions 1 25th Aug 2003 11:00 AM
Not plotting zero values Charles Delamain Microsoft Excel Charting 1 6th Aug 2003 04:38 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:43 AM.