Position of last POSITIVE value in a row

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Sep 2, 2004

• #2

Hi,

Try:

=SUMPRODUCT(MATCH(2,1/(T12:T20>0)))

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Sep 2, 2004

• #3

fairwinds said:

Hi,

Try:

=SUMPRODUCT(MATCH(2,1/(T12:T20>0)))

Click to expand...

Better:

=MATCH(2,1/(T2:T20>0))

Confirmed with control+shift+enter.

 

Upvote 0

D

Derek

Well-known Member

Joined

Feb 16, 2002

Messages

1,595

• Sep 2, 2004

• #4

Hi there

How about this code in a standard module: Select cell in row you want to check before running it:

Sub LastPositiveNumber()
If Selection.Cells.Count > 1 Then
MsgBox "Please select one cell in the row you wish to examine"
Exit Sub
End If

Dim i As Integer
For i = 256 To 1 Step -1
If Cells(Selection.Row, i).Value > 0 Then
MsgBox Cells(Selection.Row, i).Address
Exit Sub
End If
Next i
End Sub

regards
Derek

 

Upvote 0

SydneyGeek

MrExcel MVP

Joined

Aug 5, 2003

Messages

12,251

• Sep 2, 2004

• #5

Thanks guys,

Just what I needed! I went with Aladin's array formula

Denis

 

Upvote 0

SydneyGeek

MrExcel MVP

Joined

Aug 5, 2003

Messages

12,251

• Sep 13, 2004

• #6

Hi, now I have the reverse end of the problem. I can do it in 2 cells, but how do you get the location of the FIRST non-zero value in a range? The non-zeros will always be positive.

Thanks

Denis

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Sep 13, 2004

• #7

Hi,

You could:

=MATCH(1,--(B2:B20>0),0)

Ctrl + shift + enter !

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Sep 13, 2004

• #8

SydneyGeek said:

Hi, now I have the reverse end of the problem. I can do it in 2 cells, but how do you get the location of the FIRST non-zero value in a range? The non-zeros will always be positive.

Thanks

Denis

Click to expand...

=MATCH(TRUE,A2:A7>0,0)

which you need to confirm with control+shift+enter instead of just enter.

The position value that you get with this formula is a position within A2:A7, the range of interest.

If you want the native position, you need to extend...

=MATCH(TRUE,A2:A7>0,0)+CELL("Row",A2)-1

still to be confirmed with control+shift+enter.

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,336

Office Version

1. 365

Platform

1. Windows

• Sep 13, 2004

• #9

I've seen formulas like this here before =MATCH(2,1/(T2:T20>0))
and I have tried to figure out how it works, but I don't get this one. Can someone explain how this works.

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Sep 13, 2004

• #10

Aladin posted a good explanation here:

http://www.mrexcel.com/board2/viewtopic.php?t=103168&postdays=0&postorder=asc&start=0

 

Upvote 0