In a column of 121 numbers which can be positive or negative or zero, is
there a function to find the last positive value in the column?

Or do I need to build a UDF?

A couple of ways

=LOOKUP(2,1/(A1:A100),A1:A10)

entered normally or


=INDEX(A1:A10,MAX((A1:A100)*(ROW(A1:A10))))


entered with ctrl + shift & enter


note that if there is a text string in the range at the right spot it will
be returned since text is greater than number according to Excel



--


Regards,


Peo Sjoblom


"Dkline" wrote in message
...
In a column of 121 numbers which can be positive or negative or zero, is
there a function to find the last positive value in the column?

Or do I need to build a UDF?

Why not just add Isnumber to eliminate a text return:

=INDEX(A1:A25,MAX((A1:A250)*(ISNUMBER(A1:A25))*(R OW(A1:A25))))

?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
A couple of ways

=LOOKUP(2,1/(A1:A100),A1:A10)

entered normally or


=INDEX(A1:A10,MAX((A1:A100)*(ROW(A1:A10))))


entered with ctrl + shift & enter


note that if there is a text string in the range at the right spot it will
be returned since text is greater than number according to Excel



--


Regards,


Peo Sjoblom


"Dkline" wrote in message
...
In a column of 121 numbers which can be positive or negative or zero, is
there a function to find the last positive value in the column?

Or do I need to build a UDF?

I ended up creating a User Defined Function.

Dim rngPremium As Range
Dim wb As Workbook
Dim ws As Worksheet
Dim intLastPremiumYear As Integer 'last premium year
Const cintColNumber As Integer = 7 'column number - 7 is usual
Const cintColStartRow As Integer = 3 'start row of column - 3 is usual
Const cintColLastRow As Integer = 123 'start row of column - 123 is usual
Dim intCount As Integer
Sub GetLastPremium()
intCount = 0
Set wb = ThisWorkbook
Set ws = wb.Worksheets("GUI")
Set rngPremium = ws.Range(Cells(cintColNumber, cintColStartRow),
Cells(cintColNumber, cintColLastRow))
For i = cintColLastRow To cintColStartRow Step -1
intCount = intCount + 1
If Cells(i, cintColNumber) 0 Then
intLastPremiumYear = 121 - intCount + 1
ws.Range("LastPremiumYear") = intLastPremiumYear
Exit Sub
End If
Next i
Set rngPremium = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub

"RagDyer" wrote:

Why not just add Isnumber to eliminate a text return:

=INDEX(A1:A25,MAX((A1:A250)*(ISNUMBER(A1:A25))*(R OW(A1:A25))))

?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
A couple of ways

=LOOKUP(2,1/(A1:A100),A1:A10)

entered normally or


=INDEX(A1:A10,MAX((A1:A100)*(ROW(A1:A10))))


entered with ctrl + shift & enter


note that if there is a text string in the range at the right spot it will
be returned since text is greater than number according to Excel



--


Regards,


Peo Sjoblom


"Dkline" wrote in message
...
In a column of 121 numbers which can be positive or negative or zero, is
there a function to find the last positive value in the column?

Or do I need to build a UDF?