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" <[email protected]> wrote in message
news:[email protected]...
> 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