# Top 5 formula

#### Pizzio

##### Active Member
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.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.

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.

As Seti mentioned, you'd probably want to use the LARGE() and SMALL() functions. Here's a small example with LARGE():
Book1
ABCD
11232
21115
3515
4612
5311
615
732
815
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.

How would ties by handled?

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" ...??

Can you provide an example?

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.

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

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...

Replies
3
Views
293
Replies
20
Views
400
Replies
1
Views
70
Replies
5
Views
107
Replies
1
Views
165

1,196,445
Messages
6,015,299
Members
441,886
Latest member
fbell

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back