excel Formula

  • Thread starter Thread starter George A. Jululian
  • Start date Start date
G

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
 
=MATCH(MAX(B:B),B:B,0)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Many thanks it works but when i copy it down it gave the same result
the same row number which is wrong
 
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.
 
Many Many thanks for all

David Biddulph said:
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.
 
Back
Top