Finding Top 15 or Top 10% Highest Values in Excel

0
votes

So I will have a list of players (for fantasy baseball) that includes what team they are on and their salary - 3 columns in total. The number of players will range but could be in the 100-238 range. 238 is the max though.

I need to find the top 15 or 10% most expensive players, whichever is higher once we're done drafting. Therefore I will either have 15 players (if my list is <= 150) or will be the 10% once it gets to 155 and higher (since we're rounding).

Since based on my max I know I'll have anywhere from 15-24 players to pick from I had the following formula in a grid and just dragged down 24 rows. Column I is just numbered 1-24.

=IF(OR(COUNTIF($B$2:$B$501,"*")  < 150, I3 <= ROUND(COUNTIF($B$2:$B$501,"*") * 0.1,2)),INDEX($B$2:$B$211,MATCH(1,INDEX(($D$2:$D$211=LARGE($D$2:$D$211,ROWS(J$1:J1)))*(COUNTIF(J$1:J1,$B$2:$B$211)=0),),0)),"-")

However, something is wrong as I keep getting duplicate names when I test this out with test data.

Any ideas where I am wrong?

1
excelexcel-formula
In which columns are the names, teams, and salaries? – Gary's Student
Can a salary appear more than once in the list or is each salary unique? – Gary's Student
Hi Gary, Names appear in Column B, Team in C, Salaries in D. A salary can appear more than once. – user1226431

1 Answers

1
votes

The difficulty is duplicated salaries.......this can be accomodated......say the data is like:

pic

Clearly Christopher, Mark, and George all share the same salary...to untangle this in E2 enter:

=RANK(D2,$D$2:$D$23,0)+COUNTIF($D$2:$D2,D2)-1

and copy down...........this assigns a unique ID to each record.....In G2 enter:

=MATCH(ROW()-1,$E$2:$E$23,0)

and copy down...in H2 enter:

=OFFSET($B$1,G2,0)

and copy down...in I2 enter:

=OFFSET($C$1,G2,0)

and copy down...finally in J2 enter:

=OFFSET($D$1,G2,0)

and copy down...........we now have:

next

This is a "sort-by-formula"..........Pick as many names off column H as you choose!