error bar problem

[Bruce Bowler]

I'm using excel 2000

I have a sheet that I want to plot column G vs Col P, Col P has a large
number of blank cells. In the spreadsheet, when calculate the mean and
standard deviation, excel is "smart" enough to ignore the blank cells.
When I make the plot, excel is "too stupid" to realize those are "missing
values" but rather treats them as 0 which is clearly not correct.

so, 2 questions...

Did I miss an option somewhere that I can use to specify what to do with
blank cells when plotting?

Can I do an "add series" in source data where there are multiple X values
from the spreadsheet and a SINGLE Y value, also from the sheet (I know
how to do it for a constant)

Thanks
Bruce

--
+-------------------+---------------------------------------------------+
Bruce Bowler | Next to Sinatra, I have the most hostile press in
1.207.633.9600 | America. - Howard Cosell
(e-mail address removed) |
+-------------------+---------------------------------------------------+

 

[Jon Peltier]

Bruce -

Are the "blank" cells really blank? Or are they formulas that return
""? If they are truly blank, then select Options from the Tools menu,
click the Chart tab, and pick the "not plotted" option for Plot Blank
Cells As....

If they are the result of a formula, change "" to NA() in the formula,
which changes the pseudoblank to #N/A. This makes the chart look
better, but it might hose your other formulas.. In this case, you may
want to have two columns of formulas, one for subsequent calculations,
the other for charting.

- Jon

 

[Bruce Bowler]

Jon,

They are formulae, and yes, na() does "mess up" other formulae so isn't a
practical solution. The 2 column solution, while it does work (and is what
I'll do) is, IM(not so)HO, ugly...

Why, oh why, can't microsoft "do it right"??? (don't answer that one

Bruce

Bruce -

Are the "blank" cells really blank? Or are they formulas that return
""? If they are truly blank, then select Options from the Tools menu,
click the Chart tab, and pick the "not plotted" option for Plot Blank
Cells As....

If they are the result of a formula, change "" to NA() in the formula,
which changes the pseudoblank to #N/A. This makes the chart look
better, but it might hose your other formulas.. In this case, you may
want to have two columns of formulas, one for subsequent calculations,
the other for charting.

- Jon

--
+-------------------+---------------------------------------------------+
Bruce Bowler | Civilization is a race between education and
1.207.633.9600 | catastrophe. - H. G. Wells
(e-mail address removed) |
+-------------------+---------------------------------------------------+

 

[dvt]

Jon,

They are formulae, and yes, na() does "mess up" other formulae so isn't a
practical solution. The 2 column solution, while it does work (and is
what
I'll do) is, IM(not so)HO, ugly...

You have one other option. In the other formulae that are messed up,
precondition them like this:

=IF(ISERROR(cell with N/A), <appropriate response for error condition>,
<your formula>)