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

[savagek]

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

 

[savagek]

Kris,

I now get 0, 2, 2 as my answers. It seems I am getting different answers than you are. I do not understand this ??? Is there something I am doing wrong? I hit F9 to calculate the formula and I also did the Ctrl/Shift/Enter thing too!

 

[Krishnakumar]

Have a look..

Book1
	G	H	I	J	K
1	1&2	3&4	Labels	Top 3	Labels
2	71	89	1	149	5
3	141	117	2	141	3
4	134	121	3	134	4
5	149	109	4
6	94	135	5
7	77	191	6
8	90	168	7
9	0	0	0
10	0	0	0
11	0	0	0
12	0	0	0
13	0	0	0
Sheet1

In J2 and copied down,

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

In K2 and copied down,

=SMALL(IF($G$2:$G$13=J2,ROW($G$2:$G$13)),COUNTIF($J$2:J2,J2))

Confirmed with CTRL+SHIFT+ENTER

HTH

 

[savagek]

Kris,

I got it to work as if I enter the formula and hit F9 it will calculate once, but it will not calculate again if the 3 largest change. In other words, if I change any of the input numbers on the sheet, everything will change accordingly except the row numbers.

Maybe calculating a column that has the same numbers as the row numbers would be better?????

I really appreciate your help on this as I know it is getting very involved!

 

[Domenic]

=SMALL(IF($G$2:$G$13=J2,ROW($G$2:$G$13)),COUNTIF($J$2:J2,J2))

The above formula is an array formula. It needs to be confirmed with CONTROL+SHIFT+ENTER. In other words, after the formula is typed/entered, instead of confirming with just ENTER, press the CONTROL and SHIFT keys down, then while both these keys are pressed down, press the ENTER key. Excel will automatically place braces around the formula indicating that the formula has been entered correctly.

Hope this helps!

 

[savagek]

Kris and Domenic,

I looked up the array formula under help as I did not fully understand the Ctrl/Shift/Enter thing and I was entering the formula wrong. The formula now works fine. It was exactly how you guys stated I was just doing it wrong.

So thankyou so very much for all your help as I much appreciate it!

 

[Domenic]

Once you've entered and confirmed the formula with CONTROL+SHIFT+ENTER, the formula can be dragged/copied down the column like any other formula.