Hypothetical PercentRank Question

shoota306

New Member
Joined
Mar 16, 2009
Messages
3
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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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%
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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?

Disable AdBlock

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
Back
Top