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

[Guest]

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.

 

[Bernard Liengme]

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

 

[Guest]

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

 

[Guest]

Thank you guys, this was helpful.

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

 

[Harlan Grove]

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