Here's an approach that will take ties into consideration...

D2, copied down:

=RANK(B2,$B$2:$B$100)+COUNTIF($B$2:B2,B2)-1

E1:

=MAX(IF(B2:B100=INDEX(B2:B100,MATCH(F1,D2:D100,0)) ,D2:D100))-F1

...entered using CONTROL+SHIFT+ENTER.

F1: contains your Top N parameter, in this case 25

G2, copied down:

=IF(ROW()-ROW(G$2)+1<=$E$1+$F$1,INDEX(A$2:A$100,MATCH(ROW()-ROW(G$2)+1,$D$2:$D$100,0)),"")

If you want to display the corresponding information, copy this formula
across and down.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740

Domenic,

You're right. It handles ties. I don't understand the array formula in E1.
It seems to always evaluate to zero.

"Domenic" wrote:


Here's an approach that will take ties into consideration...

D2, copied down:

=RANK(B2,$B$2:$B$100)+COUNTIF($B$2:B2,B2)-1

E1:

=MAX(IF(B2:B100=INDEX(B2:B100,MATCH(F1,D2:D100,0)) ,D2:D100))-F1

...entered using CONTROL+SHIFT+ENTER.

F1: contains your Top N parameter, in this case 25

G2, copied down:

=IF(ROW()-ROW(G$2)+1<=$E$1+$F$1,INDEX(A$2:A$100,MATCH(ROW()-ROW(G$2)+1,$D$2:$D$100,0)),"")

If you want to display the corresponding information, copy this formula
across and down.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740

JBoulton Wrote:
Domenic,

You're right. It handles ties. I don't understand the array formula
in E1.
It seems to always evaluate to zero.
...

If correctly set up, it will calculate the ties of the Nth value. I
recently replaced it with an ordinary formula as shown in my reply.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273740

Aladin Akyurek Wrote:
If correctly set up, it will calculate the ties of the Nth value. I
recently replaced it with an ordinary formula as shown in my reply.

Hi Aladin!

Yes, I noticed your new formula. Interesting! :)


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740

JBoulton Wrote:
Domenic,

You're right. It handles ties. I don't understand the array formula
in E1.
It seems to always evaluate to zero.

If there is more than one value ranked 25, then all of those values
will be displayed in addition to the top 24. The formula in E1 helps
effect such a situation.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740