Row number

[G-fer]

Hi All ...

How do I find the row number of the cell that meets this criteria?

=MAX(A:A)

Regards ....

 

[VoG]

Try

=MATCH(MAX(A:A),A:A,0)

 

[G-fer]

Thanks Peter

G-fer

 

[Aladin Akyurek]

Hi All ...

How do I find the row number of the cell that meets this criteria?

=MAX(A:A)

Regards ....

B1, just enter:

=MATCH(9.99999999999999E+307,A:A)

B2, just enter:

=MAX(A1:INDEX(A:A,B1)

B3, just enter:

=COUNTIF(A1:INDEX(A:A,B1),B2)

B4: Rows Of Max

B5, control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$5:B5)<=$B$3,SMALL(IF($A$1:INDEX(A:A,$B$1)=$B$2,ROW($A$1:INDEX(A:A,$B$1))),ROWS($B$5:B5)),"")

B5 downwards you get the native row numbers.

 

[G-fer]

Ok then, that part's solved, now the harder part ...

The result of =MATCH(MAX(A:A),A:A,0) is row number 70

Now I need to assign the contents of B70 (the 70 being the result of the previous formula) to C1. I've tried things like =$B$MATCH(MAX(A:A),A:A,0) etc., but to no avail.

Any ideas?

G

 

[VoG]

Try

=INDEX(B:B,MATCH(MAX(A:A),A:A,0))

 

[Aladin Akyurek]

Ok then, that part's solved, now the harder part ...

The result of =MATCH(MAX(A:A),A:A,0) is row number 70

Now I need to assign the contents of B70 (the 70 being the result of the previous formula) to C1. I've tried things like =$B$MATCH(MAX(A:A),A:A,0) etc., but to no avail.

Any ideas?

G

What is your exact range in A:B? You can't just assume that there is just one instance of the MAX value in the A-range...

 

[G-fer]

Mmm ....

Good thought Aladin ...

A new number is appended to the bottom of row A at regular intervals. At the same time, a number is removed from the top of the numbers. Thecolumn length is continually growing, but the number of populated cells would normally remain the same. At this time there will only be a maximum of 5 populated cells in the column, and, because they run to a number of decimal places, it is extremely unlikely that there will be any dupes. however, if there were two, for example, either one of the rows returned would suffice.

My aim is to assign the value of the cell at col B, row(whatever was returned), to C1.

G-fer

 

[VoG]

See my post here http://www.mrexcel.com/forum/showpost.php?p=2886115&postcount=6

 

[G-fer]

Thanks Peter ...

That fixed it !!

G-fer