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.

 

[Seti]

Have a look at the Large/Small and Rank functions. They may be of some help. For specific help, more details will be required. Do you want them in separate cells, how will you handle ties, etc.

 

[Pizzio]

Well of course I didn't think of any of that. I for some reason assumed that I could have 5 seperate cells that would have the top 1, top 2, top 3, etc in each cell. Now the top 1-5 would be of a column I have listing out sales revenue.

 

[Tazguy37]

As Seti mentioned, you'd probably want to use the LARGE() and SMALL() functions. Here's a small example with LARGE():

Book1
	A	B	C	D
1	12		32
2	11		15
3	5		15
4	6		12
5	3		11
6	15
7	32
8	15
9
Sheet1

Formulas in C1 to C5:
=LARGE($A$1:$A$100,1)
=LARGE($A$1:$A$100,2)
=LARGE($A$1:$A$100,3)
=LARGE($A$1:$A$100,4)
=LARGE($A$1:$A$100,5)

Instead of hardcoding the numbers, you could use the ROW() function. Instead of "1", for example, use: ROW(), if you're in Row 1. When you copy down, that number will automatically increment. If you're not starting on Row 1, you'd need to subtract that row minus one. Get it? Post back if you need more help on this.

 

[Brian from Maui]

How would ties by handled?

 

[Sanador]

How would one accomplish the same task ranking names (text) rather than numbers? For example, instead of "12, 11, 5, 6, 3..." column A read "Joe, Bob, Sue, Dave, Tom" ...??

 

[Domenic]

Can you provide an example?

 

[Sanador]

I have a list of names in A11:A16 as follows:

Joe
Tom
Jim
Jan
Sue
Jon

In B11:B16 I've listed the number of times they've completed task A:

1
2
2
3
1
2

I then used the rank function to create C11:C16 as follows:

3
2
2
1
3
2

How can I then create a list of the top 5 so that A5:A9 show:

1 Jan
2 Tom
2 Jim
2 Jon
3 Joe
3 Sue

Bad example in that ther happens to only be a top 3 in this case but you get the idea... I hope.

 

[Aladin Akyurek]

I have a list of names in A11:A16 as follows:

Joe
Tom
Jim
Jan
Sue
Jon

In B11:B16 I've listed the number of times they've completed task A:

1
2
2
3
1
2

I then used the rank function to create C11:C16 as follows:

3
2
2
1
3
2

How can I then create a list of the top 5 so that A5:A9 show:

1 Jan
2 Tom
2 Jim
2 Jon
3 Joe
3 Sue

Bad example in that ther happens to only be a top 3 in this case but you get the idea... I hope.

See:

http://www.mrexcel.com/board2/viewtopic.php?t=69970

 

[Domenic]

Here's an approach that will take into consideration any ties for 5th place...

D11, copied down:

=RANK(B11,$B$11:$B$16)+COUNTIF($B$11:B11,B11)-1

E10: enter 5, indicating you want a Top 5 list

F10:

=MAX(IF(B11:B16=INDEX(B11:B16,MATCH(E10,D11:D16,0)),D11:D16))-E10

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

G11, copied down:

=IF(ROWS($G$11:G11)<=$E$10+$F$10,INDEX(A$11:A$16,MATCH(ROWS($G$11:G11),$D$11:$D$16,0)),"")

Note that if you want to display the corresponding values in Column B and Column C, enter the formula in G11, copy down and across.

Hope this helps!

P.S. I see that Aladin has provided you with a link to his Top N formula system. Since I've already used his formula system to provide you with a solution, here it is...