Empty Cell calculation
 

I have a column with formulas.
When formulas return empty cell (""), the chart seems to handle
it as a 0. I do not want to display those data points at all.
I tried to use NA() instead of €œ€� .
The chart seemed to be o.k. but now I have a problem finding MAX/MIN Values
of this column €“ because I have there now both numbers and #N/A.
Thanks in advance,
Elan

Elan,

1) Use a second column of formulas to find your MAX and MIN values

2) Use an array formula (entered with Ctrl-Shift-Enter) like

=MAX(IF(ISERROR(A1:A5),0,A1:A5))

which will ignore the #N/A values.

HTH,
Bernie
MS Excel MVP


"Elan" wrote in message
...
I have a column with formulas.
When formulas return empty cell (""), the chart seems to handle
it as a 0. I do not want to display those data points at all.
I tried to use NA() instead of "" .
The chart seemed to be o.k. but now I have a problem finding MAX/MIN
Values
of this column - because I have there now both numbers and #N/A.
Thanks in advance,
Elan

"" is not an empty cell, it is a zero length string. Worksheet formulas
cannot return empty cells. Text (including zero length strings) plot as
zero. #N/A doesn't plot, but also doesn't break connecting lines. If
you need the cell to behave EXACTLY like an empty cell, you will have to
delete the formula. For a dynamic worksheet the process of deleting or
restoring formulas as needed could be automated via a macro tied to a
change event.

Jerry

Elan wrote:

I have a column with formulas.
When formulas return empty cell (""), the chart seems to handle
it as a 0. I do not want to display those data points at all.
I tried to use NA() instead of €œ€� .
The chart seemed to be o.k. but now I have a problem finding MAX/MIN Values
of this column €“ because I have there now both numbers and #N/A.
Thanks in advance,
Elan