Last significant row in a column

D

David Berg

I need to obtain the value contained in the last row of significant values in
a column. For example, if a table has 10 rows, but Column E has significant
values (i.e. >0) in Rows 1,2,3, how do I build a function to return the value
in cell E3. I tried the function =abs(address(countif(e1:e10,">0"),5)), but
the address function returns the value <"$e$3"> (with embedded quotes); thus
the abs() function will not work because it doesn't recognize its argument as
a cell address.

Suggestions? Thank you in advance.
 
D

Domenic

Try...

=LOOKUP(9.99999999999999E+307,IF(E1:E10>0,E1:E10))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
A

Aladin Akyurek

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

Fredrik Wahlgren

Aladin Akyurek said:
=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?
Click to expand...

Yes. COUNTIF will return 4 in this case.

/Fredrik
 
F

Fredrik Wahlgren

Aladin Akyurek said:
=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?
Click to expand...

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
 
A

Aladin Akyurek

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

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:E10>0),E1:E10)
 
F

Fredrik Wahlgren

Aladin Akyurek said:
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:E10>0),E1:E10)
Click to expand...

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

/Fredrik
 
Top