-
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.
Finding the highest number
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
In C1, put the formula =IF(RANK(B1, B:B) < 6, 1, "") in C1 and drag down.
Upvote
0
Dave Patton
Well-known Member
- Joined
- Feb 15, 2002
- Messages
- 6,345
- Office Version
- 365
- 2010
- Platform
- Windows
=(B1>=LARGE($B$1:$B$10,5))+0 or
=IF((B1>=LARGE($B$1:$B$10,5)),1,"")
copy down
N.B. My post does not adjust down if there are ties; therefore edit or use Mike's.
=IF((B1>=LARGE($B$1:$B$10,5)),1,"")
copy down
N.B. My post does not adjust down if there are ties; therefore edit or use Mike's.
Last edited:
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 63,880
- Office Version
- 365
- Platform
- Windows
If you are just interested in identifying/looking at those 'top 5' rows, another option (assuming your data actually has headings) is to apply AutoFilter to your data (or at least column B) and then in the column B AutoFilter drop-down, choose
Number Filters -> Top 10 ... -> Change the boxes to Top 5 Items -> OK
That would then filter your data to only display the top 5 items (plus ties).
Number Filters -> Top 10 ... -> Change the boxes to Top 5 Items -> OK
That would then filter your data to only display the top 5 items (plus ties).
Upvote
0
Mike / Dave, both formulas work great, one question if I wanted to give these top 5 numbers a different points rating, how could I do that.
Like
Highest = 1
2nd Highest = 0.8
3rd Highest = 0.6
4th Highest = 0.4
5th Highest = 0.2
Like
Highest = 1
2nd Highest = 0.8
3rd Highest = 0.6
4th Highest = 0.4
5th Highest = 0.2
Upvote
0
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
=if(rank(b1,b:b)<6,1-(5-rank(b1:b:b))*.2, "")
Last edited:
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 63,880
- Office Version
- 365
- Platform
- Windows
Try this, copied down. You haven't identified what to do when ties occur so this is a guess in that regard.
| Book1 | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | John | 12 | |||
| 2 | Julie | 14 | |||
| 3 | Jim | 23 | 0.8 | ||
| 4 | Syeve Ryan | 8 | |||
| 5 | Helen | 32 | 1 | ||
| 6 | Tom | 9 | |||
| 7 | Tomas | 8 | |||
| 8 | Paul | 12 | |||
| 9 | Peter | 15 | 0.2 | ||
| 10 | Julie | 18 | 0.4 | ||
| 11 | June | 23 | 0.8 | ||
Top 5 | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C1 | =LOOKUP(RANK(B1,B$1:B$11),{1,2,3,4,5,6},{1,0.8,0.6,0.4,0.2,""}) | |
Last edited:
Upvote
0
