Can a cell be ignored by both a chart and a function?

[hmm]

In order for a chart to ignore a cell (i.e., not treat it as zero), I enter
the #N/A error (by typing or returning the function =NA()) in the cell. But
this will not work if I want functions such as STDEV(), MAX(), etc., to
ignore the cells; they will return an error if any cell in the argument range
is an error. For functions to ignore a cell, I must enter (or my formula
must return) a null string (="") in the cell.

Is there a way I can I satisfy the requirements of both charts and functions
to ignore a cell (without needing to maintain two separate columns, and with
as least monstrous a formula as possible)? Any insights appreciated.

 

[Del Cotter]

Is there a way I can I satisfy the requirements of both charts and functions
to ignore a cell (without needing to maintain two separate columns, and with
as least monstrous a formula as possible)? Any insights appreciated.

Not to my knowledge. Which I agree is annoying.

There is no reason why Excel should not give you the option in the
Tools.. Options.. Chart dialogue to ignore FALSE, as functions like
AVERAGE(), etc. ignore FALSE. Instead, charts treat FALSE as zero.

Your only real option is to maintain two ranges of cells, one for
calculation and table presentation, and one for charting. The one for
charting can turn all instances of FALSE or "" to N/A using a formula,
so that they will not appear in a graph.

 

[Frank Pytel]

I tried this and it worked

Set cell A1 to 1, B1 to 2, C1 to 3, etc.

In the cell that you want to have ignored, if possible, enter the following
if statement

=IF(A1="A",1,"")

I did a simple auto sum and it ignored it.

God Bless

Frank

 

[Jon Peltier]

Now plot it with a line or XY chart and you'll understand the initial
question. The "" is treated as a zero value with a corresponding data point.

- Jon

 

[Frank Pytel]

Jon;

I haven't tested it but you are more than likely absolutely correct. I
remember having a similar problem. Instead of using the =N/A() I made the
values equal to the last credible value in the data set. I haven't finished
that one yet, but I hope it will work well. If you have time and wouldn't
mind, woul you be so kind as to read the following post?

<a
href="http://www.microsoft.com/wn3/aspx/n...ting&mid=10b38c31-799c-410e-9bf5-5636d532c668"
target="_blank"><b>Read and rate the response</b></a>

I'm on my way there now to reply to a response. I am an idiot and need all
the help I can get with these math and excel formulas.

Thanks for correcting me.

Frank