empty cell

E

Elan

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
 
F

Fredrik Wahlgren

Elan said:
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
Click to expand...

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
 
B

bj

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

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.
Click to expand...

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
Click to expand...

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?
 
F

Fredrik Wahlgren

Harlan Grove said:
Fredrik Wahlgren wrote...
...

Why use a UDF rather than an array formula like

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

?
Click to expand...

Right. Your solution is nicer

/Fredrik
 
E

Elan

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.
 
F

Fredrik Wahlgren

Elan said:
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.
Click to expand...

Have you tried the UDF?

/Fredrik
 
Top