# Hypothetical PercentRank Question

#### shoota306

##### New Member
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Andrew Fergus

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

Replies
4
Views
345
Replies
4
Views
329
Replies
8
Views
373
Replies
3
Views
786
Replies
2
Views
227

1,195,598
Messages
6,010,647
Members
441,558
Latest member
lambierules

### 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.

### Which adblocker are you using?

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

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