Last Value Greater than Zero in a column

[Dkline]

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?

 

[Peo Sjoblom]

A couple of ways

=LOOKUP(2,1/(A1:A10>0),A1:A10)

entered normally or


=INDEX(A1:A10,MAX((A1:A10>0)*(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

 

[RagDyer]

Why not just add Isnumber to eliminate a text return:

=INDEX(A1:A25,MAX((A1:A25>0)*(ISNUMBER(A1:A25))*(ROW(A1:A25))))

?

 

[Dkline]

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