Posts in category: ranks

If you’re tracking standings in Excel, you know it can be challenging to ensure that you have your rankings setup correctly, especially when factoring in multiple criteria. Ranking values based on a single column or field isn’t complex, but when you start to factor in multiple conditions, it can get a little bit more complicated. Here’s a scenario I’ll attempt to solve where multiple teams have the same number of points, but where their goal difference isn’t the same:

By using an additional criteria as a tiebreaker, you can ensure you rank the teams properly. But how do you setup the formula, and what if you also want to factor in more columns and use a method which can be used for more complex situations? That’s what I’ll go over in this post.

Using the Rank function

If you were to rank these teams based on points, you would use the RANK function. You would use the value in the points field and rank it based on those values. If my first value is in F2 and the range for the points field is F2:F5, my formula would be as follows:

=RANK(F2,$F$2:$F$5)

The limitation of this method is that it will result in repeating values since multiple teams have 4 points:

How to use multiple criteria in your ranking calculations

The RANK function only allows us to base the calculation on a single field. But there is a way we can still rank the values based on multiple fields. This involves using multiple functions, and fractions. Assuming the key criteria is points, then the current formula can be kept as is. But let’s assume the second criteria is goal differential. Then, we can rank the teams based on this field, but divide the value by a factor of 10. Here’s what the formula would look like assuming goal difference is in column G:

=RANK(G2,$G$2:$G$5)/10

This then gets added to the original formula:

=RANK(F2,$F$2:$F$5)+RANK(G2,$G$2:$G$5)/10

Based on this calculation, now my formula shows an additional decimal point and I can now rank the fields based on a second field:

And now I can re-sort the data based on the rank, which correctly tells me that Team 4 is in second place when ranking them based on points, and then goal difference.

You can extend this logic to more criteria and fields. Let’s assume a more complicated scenario where the goal difference for the tied teams is 0. And instead, it comes down to a third criteria, which is their fair play score:

Assuming Fair Play is in column H, here is my updated ranking calculation:

=RANK(F2,$F$2:$F$5)+RANK(G2,$G$2:$G$5)/10+RANK(H2,$H$2:$H$5)/100

In this situation, Team 2 will have the second-best ranking because it has the best fair play score.

My ranking calculation now has yet another decimal place and another criteria which it can factor into its calculation. You can also adjust the criteria if, suppose, the lowest score for Fair Play is the best. Let’s say the lower the Fair Play score, the better the team should rank. In that case, we can adjust the formula so that we specify the rank should be in ascending order for that last criteria (the default is descending order):

=RANK(F2,$F$2:$F$5)+RANK(G2,$G$2:$G$5)/10+RANK(H2,$H$2:$H$5,1)/100

I just needed to add the final argument to the last criteria, setting it to 1. And now when I re-sort the rank, Team 4 gets the second spot:

By setting up your ranking calculation this way, you can have the flexibility of adding more fields and criteria into your ranking. If you want to add another field, simply add it to your formula and divide that value by 1000, and so forth.

If you like this post on How to Break Ties in Excel and Rank with Multiple Criteria, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

Creating a leaderboard in Excel is a great way to visually rank and compare performance, whether it’s for sales, gaming scores, or any other competitive metrics. Below, I’ll guide you through the process of creating a basic leaderboard in Excel. Here’s a table which shows player scores based on points, kills, and deaths.

Setting up a scoring system

If you just have a single metric for points to sort a leaderboard on, then all you need to do is sort the data. But in more complex situations, you may want to apply weights to calculate a score, which you can then sort. In the above table, there are points, and a kill/death ratio would could be useful in determining a total score.

You may for example, want to multiply the points total by a factor of 0.5 and multiply the kill/death ratio by 2, to get a combined score which considers both metrics. Assuming the points value is in column C and the kill/death ratio is in column F, here is how the formula for the score would look, starting with the first value in the second row:

=C2*0.5+F2*2

By calculating a weighted average, you are effectively creating a tiebreaker in the situation where the points are the same. In this example, Player 1 and Player 2 have the same points. But with a better kill/death ratio for Player 2, that player earns the higher score.

Ranking the top players

The above table isn’t sorted according to score. To sort the leaderboard, all you need to do is to click a value in the score column, and on the Data tab, click on the button to sort the values in order from Largest to Smallest. This now gives us a list that has sorted the players in order of their scores.

To refresh the data, simply click on the sort button again, which will re-sort the data.

Another option to sort the data is by using the SORTBY function. If you’re running the latest version of Excel, you’ll have access to this function. Here’s how you would enter the formula assuming the data is in a table called Table1:

=SORTBY(Table1[Player],Table1[Score],-1)

This returns a single array of data showing the player standings:

The benefit of using this formula is that the data will automatically re-sort the leaderboard for you; there is no need to click a button to sort. Since it is an array function, it will update as you enter updated values.

If you want to return the entire sorted table, you can use the following formula:

=SORTBY(Table1[[Player]:[Score]], Table1[Score], -1)

Here is what the array looks like, with some additional formatting added to it:

You can add conditional formatting to help identify high-performing players.

If you like this post on How to Create a Leaderboard and Track Standings in Excel please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.