Hypothetical PercentRank Question

[shoota306]

I have been racking my brain trying to figure out a formula and wondering if someone could help me.

I have a data set that I am trying to hypothetically get the percent rank of a number within a subset of a larger set of data. Essentially a RANKIF function or sorts.

Sheet 1:
Name Team Name Total Percent Rank
One Red 25.50 ?

Sheet 2:
Contains the same columns as sheet one but for name on every team in the league with their name, team, totals.

What I would like to do is in the percent rank row be able to rank person that is not currently on the team within a specified team. Sort of use a percent rank and vlookup in one statement.

Rank "25.50" within all the totals for team green on sheet 2.

Any guidance would be awesome!

 

[Andrew Fergus]

Hi and welcome to MrExcel

I'm pretty sure what you want can be done but I can't visualise the problem. Are you able to provide some sample data, and the result you want to show? And in partcilar, how you want the ranking to be evaluated? Check the FAQ for instructions on how to load an image of your workbook.

Andrew

 

[shoota306]

I am sorry I didn't make the question clear. My problem is that my download has 3 columns (Name (Column A), Team Name (Column B), Total (Column C)) and I need to calculate what percentage of their team that they beat (Column D). So for instance Chuck who is on the black team with a total of 10.60 beat 0% of his teammates.

I have been doing this by sorting and changing the cell reference rows for each team. This can be tedious as I have a large # that I need to do this for.

What I would like to do is in the Percent Rank column (D) perform a similar task to the =SUMIF(B:B,B2,C:C).

Name,Team Name,Total,PercentRank
Chuck,Black,10.60,0%
Dave,Green,19.65,67%
Harry,Blue,28.10,67%
Jim B,Blue,31.00,100%
Jim G,Green,19.25,33%
John,Black,19.77,67%
Larry,Blue,26.50,33%
Lisa,Red,16.75,33%
Melissa,Red,32.11,100%
Michelle,Green,25.66,100%
Mike,Red,25.50,67%
Paul,Black,15.00,33%
Peter,Black,29.80,100%
Renee,Red,14.65,0%
Rich,Green,12.96,0%
Walter,Blue,24.85,0%

 

[Andrew Fergus]

Hello

Thanks for that - this is much clearer!

Try using the following formula in cell D2 (assuming your data as shown starts at cell A1 with the heading 'Name'):

=SUMPRODUCT(--(B2=$B$2:$B$18), --(C2>$C$2:$C$18)) / (COUNTIF($B$2:$B$18,B2) -1)

and copy this formula down. Change the reference to row 18 (in 3 places) to however many rows you actually need for your data, e.g. you may have 50 or 100 rows of data.

I trust that helps
Andrew