"Aladin Akyurek" wrote in message
...
Fredrik Wahlgren wrote:
"Aladin Akyurek" wrote in message
...

=ABS(INDIRECT(ADDRESS(COUNTIF(E1:E10,"0"),5)) )

returns

0

when:

E1: 1
E2: 3
E3: 3
E4: 0
E5: 3.4

E6 to E10 are empty.

Is the result of 0 this expensive formula returns correct?



The best I can think of is a UDF. This one should work. It just loops
over
the passed range and puts the value of each cell in the range as long as
it
is <0.

Public Function test(ByVal r As Range) As Variant
On Error Resume Next
Dim Cell As Range
Dim v As Variant

For Each Cell In r
If 0 < Cell.Value Then
v = Cell.Value
End If
Next

If vbDouble = VarType(v) Then
test = Abs(v)
Else
test = 0
End If
End Function

/Fredrik



I did not try the UDF you suggest. Does it compute something different
from the formula Domenic suggested in his reply or its equivalent:

=LOOKUP(2,1/(E1:E100),E1:E10)


I haven't tested that. If it works OK, use his suggestion. If nothing else,
it's easier to use.

/Fredrik