A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Impossible? Not plotting NA()s



 
 
Thread Tools Display Modes
  #1  
Old March 31st, 2010, 11:45 AM posted to microsoft.public.excel.charting
LiAD
external usenet poster
 
Posts: 194
Default Impossible? Not plotting NA()s

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

  #2  
Old March 31st, 2010, 02:35 PM posted to microsoft.public.excel.charting
Luke M[_4_]
external usenet poster
 
Posts: 451
Default Impossible? Not plotting NA()s

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
...
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



  #3  
Old March 31st, 2010, 04:01 PM posted to microsoft.public.excel.charting
LiAD
external usenet poster
 
Posts: 194
Default Impossible? Not plotting NA()s

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
...
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



.

 




Thread Tools
Display Modes

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

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:54 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.