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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|