Trying to rank a Win-Loss Record based on three different criteria

[RCope]

Hello all!

Here is my question: Column A has a list of team names, column B has formulas to dynamically update the Wins, and column C has formulas to dynamically update the losses of the team in the same row. Now I need to be able to enter formulas into cells D,E, and F that rearrange A,B, and C from teams with best record to teams with worst record.

The criteria are: 1st - Teams with most wins. 2nd - All teams with same amount of wins, arrange depending on lowest amount of losses (small being best), and 3rd - All teams with exact same wins and losses, arrange in alphabetical order of team name. Is this possible?

I don't have a workbook to upload because I am on another computer, but here is how to easily arrange a simple workbook to help:

A1:A6=
Boston College
Clemson
Florida State
Maryland
NC State
Wake Forest

B1:B6 (Wins)
6
8
7
6
7
7

C1:C6 (Losses)
6
4
4
5
4
5

Then I want to be able to enter formulas into D1:F8 to rearrange the data. TIA!

[JBeaucaire]

Here's one way...

[RCope]

That is great JB!

Would you mind explaining it to me briefly? I would love to know you were able to make this work. Thanks again!

[JBeaucaire]

The formulas are there. You can see how the WINS/LOSSES were combined to create precision decimal values. That is the number that is then used to RANK the original table.

The RANKS also count how many times that "precision value" has appeared so far in the table as the formulas go down, so any time there is a duplicate (tie-tie) then the table listing causes the RANK to not repeat. NC State has the same precision value of .0704, so the COUNTIF() part of that formula in E6 gets a count of 2, minus 1 will add 1 to the rank of 3 it wants to give to that value, causing the final result to be 4. No ties.

Then the new table simply uses a MATCH to the RANKS.


You can click on any of these formula cells and use the Evaluate Formula. On the Ribbon it is found under Formulas > Formula Auditing > Evaluate Formula


If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[RCope]

Thanks Pal, you were all over it. I am very pleased! Well done