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