Top 5 formula

[Pizzio]

Is there a way to create a formula in excel that pulls from a column the top/bottom 5 entries out of 100 items? I've been working on this for a day now.

 

[Sanador]

I've used the following formula for my ranks:

=RANK(AC12,$AC$12:$AC$55,0)

where AC12:AC55 are the indivdual "scores." I then used the following to list my top 5:

=IF(ROW()-ROW($L$2)<=$L$1,INDEX($AB$12:$AB$55,MATCH(ROW()-ROW($L$2),$AD$12:$AD$55,0)),"")

where L1="5" and the top 5 are listed in L3:L7 and AB12:AB55 are the individual's names.

This works well except it does not account for ties at any position. I'd like the top 5 to list as follows:

1. Joe (ranked 1, score 5)
2. Tim (ranked 2, score 3)
2. Sue (ranked 2, score 3)
4. Jim (ranked 4, score 2)
4. Jon (ranked 4, score 2)

Is there an easy method of accomplishing this? Items in parentheses are not required. Thanks for all the help!

 

[Aladin Akyurek]

I've used the following formula for my ranks:

=RANK(AC12,$AC$12:$AC$55,0)

where AC12:AC55 are the indivdual "scores." I then used the following to list my top 5:

=IF(ROW()-ROW($L$2)<=$L$1,INDEX($AB$12:$AB$55,MATCH(ROW()-ROW($L$2),$AD$12:$AD$55,0)),"")

where L1="5" and the top 5 are listed in L3:L7 and AB12:AB55 are the individual's names.

This works well except it does not account for ties at any position. I'd like the top 5 to list as follows:

1. Joe (ranked 1, score 5)
2. Tim (ranked 2, score 3)
2. Sue (ranked 2, score 3)
4. Jim (ranked 4, score 2)
4. Jon (ranked 4, score 2)

Is there an easy method of accomplishing this? Items in parentheses are not required. Thanks for all the help!

Recap...

aaTopN Sanador.xls
	K	L	M	N
1		5
2		2
3		Top 5
4		joe
5		tim
6		sue
7		dawn
8		john
9		jim
10		jon
11
Data

aaTopN Sanador.xls
	AB	AC	AD	AE
10
11	Name	Score	Rank
12	dawn	2	4
13	damon	1	8
14	bob	1	9
15	chris	0	12
16	christine	1	10
17	ian	1	11
18	john	2	5
19	joe	5	1
20	tim	3	2
21	jim	2	6
22	jon	2	7
23	sue	3	3
Data

L1: 5

L2:

=MAX(IF(INDEX(AC12:AC55,MATCH(L1,AD12:AD55,0))=AC12:AC55,AD12:AD55))-L1

which is confirmed with control+shift+enter.

L4, copied down:

=IF(ROW()-ROW($L$4)+1<=$L$1+$L$2,INDEX($AB$12:$AB$55,MATCH(ROW()-ROW($L$4)+1,$AD$12:$AD$55,0)),"")

AD12, copied down:

=RANK(AC12,$AC$12:$AC$55)+COUNTIF($AC$12:AC12,AC12)-1