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

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.

On Sun, 22 Jul 2007, in microsoft.public.excel.charting,
hmm said:
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.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

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

"hmm" wrote:

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.

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Frank Pytel" wrote in message
...
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

"hmm" wrote:

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.

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/notifauth.aspx?url=http://www.microsoft.com/office/community/en-us/default.mspx%3fdg%3dmicrosoft.public.excel.chartin g%26mid%3d10b38c31-799c-410e-9bf5-5636d532c668"
target="_blank"bRead 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

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Frank Pytel" wrote in message
...
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

"hmm" wrote:

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.