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

 

[MrKowz]

Welcome to the forums!

Try the following:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">12</td><td style="text-align: right;;">32</td><td style="text-align: right;;">25</td><td style="text-align: right;;">18</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Last Value</td><td style="text-align: right;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">{=INDEX(<font color="Blue">A1:J1,MATCH(<font color="Red">9.99E+307,IF(<font color="Green">A1:J1>0,1,""</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[Sektor]

Are values in one cell or in different cells?

 

[diddi]

hi and welcome to the board

 

[adamash]

Thanks for the welcome, although I'm not new I just lost my username and couldn't get the password reset to work, so re-registered.

Thank you for your help, this has worked! Have a good day

Welcome to the forums!

Try the following:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">32</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Last Value</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B3</TH><TD style="TEXT-ALIGN: left">{=INDEX(A1:J1,MATCH(9.99E+307,IF(A1:J1>0,1,"")))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>

 

[MrKowz]

Thanks for the welcome, although I'm not new I just lost my username and couldn't get the password reset to work, so re-registered.

Thank you for your help, this has worked! Have a good day

Glad it worked! Sorry to hear you lost your old username. Thanks for the feedback though!

 

[Jonmo1]

Thanks for the welcome, although I'm not new I just lost my username and couldn't get the password reset to work, so re-registered.

You might try contacting the Administrator, they will probably be able to reset it for you..

http://www.mrexcel.com/forum/sendmessage.php

 

[Jameo]

I'd write a macro:

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 Sheets("SheetName").Cells(i, IntCol) > CuVal 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

Change the "SheetName" to the name of your sheet.
The macro will work out the highest number for every row of data, and poulate teh highest number at the end of the row of data

 

[Jameo]

Or a simpler way

=max(range)

 

[adamash]

Thanks for the other help offered guys, appriciate it.

You might try contacting the Administrator, they will probably be able to reset it for you..

http://www.mrexcel.com/forum/sendmessage.php

Thanks Jonmo, I have sent an email. The problem I had was my email address I registered with was an old work one that I don't have access too - I've emailed so hopefully I can get it sorted