excel Formula

[George A. Jululian]

Hi

column B from b1 till b500 is full of numbers and I have formula in column a
=LARGE($B$1:$B$500,1)

I want to find the result of the formula came from which row number

please help

 

[excelent]

=MATCH(A1,$B$1:$B$500,0)


"George A. Jululian" skrev:

 

[Bob Phillips]

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

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[George A. Jululian]

it did not work the result gave wrong row number

 

[George A. Jululian]

Many thanks it works but when i copy it down it gave the same result
the same row number which is wrong

 

[David Biddulph]

What are you trying to achieve by copying the formula down? You asked for
the row number in which the largest value in the range appears.

If in copying down you want to start by applying the formula to the range
B1:B500 and then want to apply it to a new range B2:B501, you could change
Bob's formula from =MATCH(MAX(B:B),B:B,0) to
=MATCH(MAX(B1:B500),B1:B500,0) and then copy that down.#

If, instead of that you wanted to find in row 1 the row number for the
largest, but in the next row the row number for the second largest, and so
on, then change Bob's formula to =MATCH(LARGE(B:B,ROW(A1)),B:B,0) and copy
down.

 

[George A. Jululian]

Many Many thanks for all

What are you trying to achieve by copying the formula down? You asked for
the row number in which the largest value in the range appears.

If in copying down you want to start by applying the formula to the range
B1:B500 and then want to apply it to a new range B2:B501, you could change
Bob's formula from =MATCH(MAX(B:B),B:B,0) to
=MATCH(MAX(B1:B500),B1:B500,0) and then copy that down.#

If, instead of that you wanted to find in row 1 the row number for the
largest, but in the next row the row number for the second largest, and so
on, then change Bob's formula to =MATCH(LARGE(B:B,ROW(A1)),B:B,0) and copy
down.