empty cell

[Guest]

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

 

[Fredrik Wahlgren]

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

You can use a UDF like the one below. It will ignore things in the passed
range which are not numbers.

Public Function myMax(ByVal r As Range) As Variant
On Error Resume Next
Dim cell As Range
Dim maxval As Double
Dim ValueFound As Boolean

maxval = -999999999999#
ValueFound = False

For Each cell In r
If cell.Value > maxval Then
maxval = cell.Value
ValueFound = True
End If
Next cell

If ValueFound Then
myMax = maxval
Else
myMax = CVErr(xlErrNum)
End If
End Function

 

[Guest]

you can use = max(if(iserror(a1:a20,"",a1:a20)
changing the range to what you need
You can use the min function similarily

 

[Harlan Grove]

Fredrik Wahlgren wrote...
....

You can use a UDF like the one below. It will ignore things in the passed
range which are not numbers.

Why use a UDF rather than an array formula like

=MAX(IF(ISNUMBER(rng),rng))

?

More generally, if there's missing data in chart series, better to use
separate ranges for chart series and downstream calculations, with the
chart ranges derived from the downstream calculation ranges so that
nonnumbers are converted to #N/A.

Public Function myMax(ByVal r As Range) As Variant

If there were good reason to use a udf, why not make it as flexible as
the built-in MAX function? That is, why isn't the argument a
ParamArray, which would allow variable numbers of arguments as well as
individual numbers and arrays as well as ranges?

 

[Fredrik Wahlgren]

Fredrik Wahlgren wrote...
...

Why use a UDF rather than an array formula like

=MAX(IF(ISNUMBER(rng),rng))

?

Right. Your solution is nicer

/Fredrik

 

[Guest]

Thanks for your answers, but I still could not calculte Max/min values.
for example: A1=2 A2=8 A3=4 A4=#N/A
how can i find max value?
Note: I can not add another column.

 

[Fredrik Wahlgren]

Thanks for your answers, but I still could not calculte Max/min values.
for example: A1=2 A2=8 A3=4 A4=#N/A
how can i find max value?
Note: I can not add another column.

Have you tried the UDF?

/Fredrik