Our Scenario

We set this up recently for a volleyball league. To give you a bit of background so you can compare this to what you need to do, here are the details. This league ranks its teams based on one piece of information first and if there is a tie it moves onto a second piece of information. The first piece of information is the win percentage. How many games has the team played compared to how many they have won. If that is tied with another team, it then moves onto the point differential. The point differential takes the points they have earned and subtracts the points other teams have earned against them. You can use any information as the first comparison and then the second for tie breaking, but this is our scenario to give you some context.

Setting Up The Team Standings

SP (Sets Played): First we have to tally up the amount of sets they have played. We are counting these from the other two sheets we are using to record the scores within the games. The Upper division sheets and the Lower division sheet.

W (Wins): Wins will go through those sets played and see which ones are the winning sets. Our league caps the games at a certain score for time restriction purposes. So we check to see if the first two sets have reached 25 and the third reached 15. If any of those are 25 or 15, that team has won the set. If you aren’t capping your sets, you will have to write the calculation to figure out which team actually won.

W% (Win Percentage): Divide the wins by the sets played to get the teams win percentage.

PF (Points For): Tally the points the team has earned.

PA (Points Against): Tally the points other teams have earned against them. This is a little more complicated depending on how you set up your spreadsheet. In ours, when we record the scores, we require our stats volunteer to enter the number of the opponent in from of each match so we can gather these.

PD (Point Differential): Subtract the Point Against from the Points For.

Figuring Out Where Teams Rank

Now we have to figure out where the teams actually rank in the list. Excel has a RANK function but it has been replaced by RANK.EQ. This is what we will start with.

Excel’s Description: Returns the rank of a number in a list of numbers. Its size is relative to other values in the list; if more than one value has the same rank, the top rank of that set of values is returned. If you were to sort the list, the rank of the number would be its position.

Syntax: RANK.EQ( number, ref, [order] )
The RANK.EQ function syntax has the following arguments:
Number: Required. The number whose rank you want to find.
Ref: Required. An array of, or a reference to, a list of numbers. Non-numeric values in Ref are ignored.
Order: Optional. A number specifying how to rank number.

We set up our function as follows:

=RANK.EQ($E5, $E$5:$E$18)

The number will be $E5. This is the first Win Percentage in our list since we are trying to figure out the rank of the first team in the list. We have the dollar sign in from of the E to make this a mixed reference. That means no matter where this cell is copied or autofilled to this will always reference column E. Since there isn’t a dollar sign in from of the 5, that would be left to change when the cell is copied or autofilled. This means when we are complete this first one, we can autofill it for the rest and it will use the proper cells it requires.

The ref will be set to all of the cells that hold all of the win percentages for the teams. This lets the function compare the number argument to the other win percentages. We have these all setup with two dollar signs per reference so that when we copy or autofill these, they will not change at all. We want it to always use this same group of win percentages for every comparison.

With this only checking the win percentage column we run into a problem pretty quick. When there are ties it doesn’t know what to do and we end up with a few ranked as 1.

Breaking Ties

To break ties we will add an extra number to the rank based on whether there are more than one sitting in that same position. To do this we will use the COUNTIFS function.

Excel’s Description: The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

Syntax: COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2]… )
The COUNTIFS function syntax has the following arguments:
criteria_range1: Required. The first range in which to evaluate the associated criteria.
criteria1: Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, “>32", B4, “apples", or “32".
criteria_range2, criteria2, …: Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed. Important: Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other.

As you can see in the image above, with the COUNTIFS setup in its own cell to the right it can see when two teams are ranked as 1. It will take the first team it finds, compare it to the given criteria and put a 0 next to it. The second team ranked as 1
gets a 1 next to it and so on. We will take these results and add them to the rank to count up. 1 will still be 1, but the second 1 will now be 2 with the COUNTIFS result added to it.

=COUNTIFS($E$5:$E$18, $E5, $H$5:$H$18, ">" & $H5)

It will take the range holding the Win Percentages and compare them to the current rows Win Percentage to see if it exists in the list multiple times. It will then take the Point Differential and compare the current rows PD against the others that match to see if it is larger. If it is, it will rank higher.

The Final Solution

The final solution is as follows:

=RANK.EQ($E5, $E$5:$E$18) + COUNTIFS($E$5:$E$18, $E5, $H$5:$H$18, ">" & $H5)

To download the Excel sheet to see how it all works, click the link below:

Microsoft Excel Team Ranking With Tie Breaking

Hope this helps you out and saves you a few headaches!