return row number of the top 3 largest values ???

savagek · Dec 15, 2006

Hello,

I am trying to return the row number of the top 3 largest numbers in a column. I am using the formulas =LARGE(J1:J12,1) , =LARGE(J1:J12,2) & =LARGE(J1:J12,3) and this works fine, but I need to return the row number of the value found. For instance, if the largest value is located in J7, I need to return 7 (the row #).

Please let me know if this is possible and thanks for your time,

Rob

venkat1926 · Dec 15, 2006

instad of using the formula try this alternae method

1.autofilter
2. click the arrow of filter of that column and choose top 10
3. in the resulting window change 10 to 3
the filtered values will show the row numnber.
if you want to make use of these row numbers as variable I think you have t o write a macro

Krishnakumar · Dec 15, 2006

Hi,

Book2

J

K

L

M

N

1

Large #s

row #

Large #s

row #

2

22

99

4

43

5

17

6

37

7

99

8

61

9

7

10

99

11

71

12

2

13

95

Sheet1

Formulas:

Ignoring the ties

In K2,

=MAX(J2:J13)

In K3 and copied down,

=MAX(IF($J$2:$J$13< K2,$J$2:$J$13))

Confirmed with Ctrl+Shift+Enter

L2 and copied down,

=MATCH(K2,$J$2:$J$13,0)+1

Considering ties,

M2 and copied own,

=LARGE($J$2:$J$13,ROWS($A$2:$A2))

N2 and copied down,

=SMALL(IF($J$2:$J$13=M2,ROW($J$2:$J$13)),ROWS($A$2:$A2))

Confirmed with Ctrl+Shift+Enter

HTH

savagek · Dec 16, 2006

Kris,

I cannot seem to get the row portion to work. The large count works fine
as I need to pick up the duplicate high numbers. Below are the results I get under different conditions:

Large Row
149 1
149 #NUM!
149 #NUM!

Large Row
149 0
141 #NUM!
134 #NUM!

I used the formulas:

=LARGE($J$2:$J$13,ROWS($A$2:$A2))

=SMALL(IF($J$2:$J$13=M2,ROW($J$2:$J$13)),ROWS($A$2:$A2))

Please let me know of a possible solution? Also, thanks for your help and your time.

Rob

SIXTH SENSE · Dec 16, 2006

hi!

try this.
change the large portion 1 to 3. this assumes that your data starts from row 1. else add an offset value equal to the starting row of the data.
Ties will be rank diffrently.

Code:

=MATCH(LARGE(A16:A20,1),A16:A20,0)

Aladin Akyurek · Dec 16, 2006

Hello,

I am trying to return the row number of the top 3 largest numbers in a column. I am using the formulas =LARGE(J1:J12,1) , =LARGE(J1:J12,2) & =LARGE(J1:J12,3) and this works fine, but I need to return the row number of the value found. For instance, if the largest value is located in J7, I need to return 7 (the row #).

Please let me know if this is possible and thanks for your time,

Rob

Why do you want to return the row numbers?

Krishnakumar · Dec 16, 2006

Post your data using Colo's HTML Maker

savagek · Dec 16, 2006

Hi Kris,

I did as you said and posted the html code but for some reason it is not showing up. After reading much information on this sugject here, I determined it was done correctly so I reported the post.

Anyway, I put the results of the Html maker on my server at the address below. This is the portion of the formula I am having trouble with. please check it out and let me know your thoughts. Thanks again!

http://www.premiermax.com/formula.htm