help! formula to get the last value in a column & a formula to get first value in a column

Posted by denis on January 01, 2002 10:06 AM

Do you know a formula to get the end cell value in column C i.e. c1 has 100 in it , c2 c3 c4 have no contents, c5 has 99 in it , then the formula should return the value 99.

Happy New Year & Thanks

Posted by Aladin Akyurek on January 01, 2002 10:34 AM

Denis --

=INDEX(C:C,MATCH(9.99999999999999E+307,C:C)

will give you the last numeric value in column C.

In order to get the first numeric value in column C, you need to add the following user-defined function to your workbook:

Function Used(r As Range) As Range
'
' Harlan Grove
' Sun 25 Nov 01
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
End Function

and use the following formula:

=INDEX(C:C,SUMPRODUCT(MIN(IF(ISNUMBER(USED(C:C)),ROW(USED(C:C))))))

Aladin