Leaving a cell blank. Not NA(), not "".
 

Is there any way to create a truely blank cell that is ignored by a chart AND
by another function e.g AVERAGE or STDEV.
NA() works with charts, but not with AVERAGE. "" works with AVERAGE but can
not be scatter-plotted.

An old problem perhaps.

EITHER
Blanks are ignored by AVERAGE
For charts: make chart, click on it; use Tools|Options; open Chart tab; set
"Plot empty cells as" to Interpolate
OR
Use NA() and get average with =AVERAGE(IF(ISNA(A1:A100),"",A1:A100)) but
enter this with Shift+Ctr;+Enter as it is an array function

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Incoherent" wrote in message
...
Is there any way to create a truely blank cell that is ignored by a chart
AND
by another function e.g AVERAGE or STDEV.
NA() works with charts, but not with AVERAGE. "" works with AVERAGE but
can
not be scatter-plotted.

An old problem perhaps.

The rather obvious response is - if there is something in the cell, it cannot
be truly blank.

You can deal with NA() in an average by using an array formula, committed
with Ctrl-Shift-Enter:

=AVERAGE(IF(NOT(ISNA(C1:C60)),C1:C60))


"Incoherent" wrote:

Is there any way to create a truely blank cell that is ignored by a chart AND
by another function e.g AVERAGE or STDEV.
NA() works with charts, but not with AVERAGE. "" works with AVERAGE but can
not be scatter-plotted.

An old problem perhaps.

Thank you guys, this was helpful.

Next question: how to do the same thing with the LINEST function.

Bernard Liengme wrote...
....
For charts: make chart, click on it; use Tools|Options; open Chart tab; set
"Plot empty cells as" to Interpolate
....

I believe you're missing the OP's point. The OP wouldn't have a problem
if the cells in his/her chart range were truly blank, which would be
why the OP is asking how to generate blank cells. If the OP is using ""
as a proxy for blank, then the chart option you mention doesn't help -
Excel *ALWAYS* plots text values as zeros. REALLY STUPID, but this just
wouldn't be Excel if there weren't a few (dozen? hundred?) stupid
design 'features'.