Tom’s Tutorials For Excel: Ranking With Ties
You may have come across situations where you need to rank a series of numbers, such as a list of test scores or elapsed times in a footrace. The RANK function does a good job of indicating the scores in order of first, second, third, and so on. But what if there are ties among the scores being ranked?
In the following pictures, the table in range A4:C13 represents a static list of players, scores, and a scale range of 1 to 10 to accommodate an available ranking slot for each of the 10 players.
In this first picture, range E4:E13 is selected to call attention to the formula in cell E4 that uses a simple RANK function of the scores from range B4:B13. The formula is =RANK(B4,$B$4:$B$13).
Notice that the formula does not return a 3 or a 9 because there are two sets of ties among the scores: Tom and Sophia both scored 245, while Mike and Nick both scored 138.
You can break a ranked tie in either an ascending or descending fashion. Suppose the scoring table is an average of three rounds of golf where the lowest score is the best score and would be ranked #1. In that case, Bob would be ranked #1 because he had the best (lowest) golf score as shown in the next picture with the formula =RANK(B4,$B$4:$B$13,1)+COUNTIF($B$4:B4,B4)-1
If the scores were from a bowling match, where the highest score is the winner and deserving of the #1 rank, then Angelina’s score of 256 would be the winner. In the next picture, that formula is:
=RANK(B4,$B$4:$B$13,0)+COUNTIF($B$4:B4,B4)-1
The table highlighted in blue shows how to display the name of the person, instead of the ranking, to help identify the players according to their rank.
The following picture shows the names where a raw rank score exists. Because there is no rank returned for #3 or #9, an error will occur with ties, using the formula
=INDEX($A$4:$E$13,MATCH(C4,$E$4:$E$13,0),1)
To avoid errors, you can refer to the ascending or descending rankings in the yellow section, such as this formula for ascending names…
=INDEX($A$4:$F$13,MATCH(C4,$F$4:$F$13,0),1)
…and this formula for descending names:
=INDEX($A$4:$G$13,MATCH(C4,$G$4:$G$13,0),1)
Tom: I’m trying to rank a 4 team softball pool, but having difficulty. Each team plays 3 games, and there could be 4 different win/loss outcomes: Group 1: 3-0, 2-1, 1-2, 0-3; group 2: 3-0, 1-2, 1-2, 1-2; group 3: 2-1, 2-1, 1-2, 1-2; and group 4: 2-1, 2-1, 2-1, 0-3. Any suggestions on what Excel formulas to use to determine the rank would be appreciated. Two team ties can be decided by head-to-head competition, three way ties we would use run differential and head-to-head. But I’m struggling with the formulas.
Thanks
Just thinking out loud, I would consider building a model to accommodate two factors in the softball games, not just one factor as the pictures show in my example. Wins can be one factor and runs scored can be another factor, the latter for no other reason than to generate incentive for a team that is winning to keep pushing their productivity.
I would attach (let’s say) 2 “points” for a win and 1 “point” for every run scored. Because it is baseball, the games cannot end in a tie. I would add an “combined points” column similar to the standings you see in the world cup soccer (or football, depending on where you live). That one “combined points” column can be the one to be ranked, using one of the pictured examples in my original post.
Those are my first thoughts. The tougher challenge is, you’d need the teams to agree to the notion of added value to runs scored, especially in baseball with its unwritten rule of (which personally I detest) calling off the dogs in the late innings when leading by some baseball-OK-sensitive scoring lead.