Last Value Greater than Zero in a column
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? |
Last Value Greater than Zero in a column
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? |
Last Value Greater than Zero in a column
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? |
Last Value Greater than Zero in a column
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? |
All times are GMT +1. The time now is 02:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com