Last significant row in a column
 

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.

Try...

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

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

Hope this helps!

In article ,
"David Berg" wrote:

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.

How about

=abs(INDIRECT(address(countif(e1:e10,"0"),5)))


"David Berg" wrote:

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.

Thank you, Duke! That's exactly what I need.

"Duke Carey" wrote:

How about

=abs(INDIRECT(address(countif(e1:e10,"0"),5)))


"David Berg" wrote:

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.

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

David Berg wrote:
Thank you, Duke! That's exactly what I need.

"Duke Carey" wrote:


How about

=abs(INDIRECT(address(countif(e1:e10,"0"),5)) )


"David Berg" wrote:


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.

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


Yes. COUNTIF will return 4 in this case.

/Fredrik

"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

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)

"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