Return last value in a row > 0

[adamash]

Guys.

I have a query for you.

I have a row of values. I need a formula that will return the last value > 0

For example;

0 12 32 25 18 0 0 0 0 0

The formula should return 18

Is this possible in Excel?

Many thanks,
Adam

 

[Jonmo1]

Or a simpler way

=max(range)

The request was for the last (furthest to the right) value that is > 0
This is not necessarily the largest # in the range.

Given the OP's example..
max(0 12 32 25 18 0 0 0 0 0)
Max would return 32
OP wants 18

 

[Jameo]

Oops - note to self, read the question twice

 

[T. Valko]

Guys.

I have a query for you.

I have a row of values. I need a formula that will return the last value > 0

For example;

0 12 32 25 18 0 0 0 0 0

The formula should return 18

Is this possible in Excel?

Many thanks,
Adam

Here's another one...

=LOOKUP(2,1/A1:J1,A1:J1)

 

[Jameo]

Even though you now have all the answers you need, I have modifed my original macro to reflect teh true nature of your question

Sub test()
'Assuming your data starts in cell A2
Dim IntCol As Long
Dim LCol As Long, LCol1 As Long
Dim LRow As Long
Dim CuVal As Long
Dim i As Long
IntCol = 1
CuVal = 0
LCol = Sheets("SheetName").Cells(1, Columns.Count).End(xlToLeft).Column
LCol1 = Sheets("SheetName").Cells(1, Columns.Count).End(xlToLeft).Column + 1
LRow = Sheets("SheetName").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LRow
    Do
    
        If Not Sheets("SheetName").Cells(i, IntCol) = 0 Then
        
        CuVal = Sheets("SheetName").Cells(i, IntCol)
        
        End If
    
    IntCol = IntCol + 1
    Loop Until IntCol = LCol
Sheets("SheetName").Cells(i, LCol1).Value = CuVal
Next i
End Sub

Regards