-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Top 5 formula
- Thread starter Pizzio
- Start date
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.
Upvote
0
Tazguy37
MrExcel MVP
- Joined
- May 28, 2004
- Messages
- 4,237
As Seti mentioned, you'd probably want to use the LARGE() and SMALL() functions. Here's a small example with LARGE():
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.
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.
Upvote
0
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.
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.
Upvote
0
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...
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...
Upvote
0
