Ranking with Tiebreakers

Using tiebreakers is frequently required in sports leagues and sales data to come up with winners and losers. We can use a clever trick to rank with tiebreakers, a process otherwise made difficult by the many conditions.

Here we have sales data for automobile salesmen, and we want to grant Christmas bonuses based on their performance. The first statistic we want to use to rank them is the Sales Level. If there is a tie in that statistic, we then want to rank the salesmen by the number of Extended Warranties they sell. If there is still a tie, we then want to rank by the number of Sports Packages sold. And finally, if there is still a tie, we then want to rank by Rustproofing sales. Ties after that will have to remain ties (or we can break ties by random numbers like the NHL does).

So in our sample data, Qay and Pat are tied in all categories except the final one. Here Qay beats Pat for Number One by virtue of having sold one more Rustproofing contract to some poor dope. Of course, that makes Roy sit at Number 3 because Qay and Pat both sold more Sports Packages – his Rustproofing sales are greater than theirs but are defined to be of lesser importance in this performance assessment process.

What can be done in Excel to rank according to this kind of hierarchy? One solution is to create a comprehensive statistic for each salesman. This new stat we can call Grade. After we create the Grade stat we can simply use function RANK.EQ to rank our salesmen and write them Christmas cheques. The algorithm requires creating a few helper columns in our official Excel Table (here called Auto_Sales), each one being a multiplier of the four relevant categories. Note that other data sets can have more or fewer tie-breaking categories, to be sure.

It is necessary to reckon the multipliers by how many significant digits they have, else they can “tick over” and produce an incorrect ranking. It is crucial to have the algorithm automatically account for these lengths because they might change when new data is added to the table. For example, someone might sell more than $100,000 worth of Extended Warranties, which would alter all the orders of magnitude.

For Rustproofing, the longest length is 3, meaning that for this data Rustproofing never equals or exceeds 1000 contracts. Similarly, for Sports Packages the longest length is 4, meaning that for this data Sports Packages never equals or exceeds $10,000. Similarly, for Extended Warranties the longest length is 5, meaning that for this data Extended Warranties never equals or exceeds $100,000. Lastly, for Sales Level the longest length is 2, meaning that for this data Sales Level never equals or exceeds level 100.

Putting it all together, we multiply Sales Level by the sum of the orders of magnitude of its inferior categories, or 10^(5+4+3) = 1,000,000,000,000. In that fashion we then multiply Extended Warranties by 10^(4+3)= 10,000,000. Similarly, we multiply Sports Packages by 10^(3)= 1,000. And we multiply Rustproofing by 1 (since it’s the final, least-weighted category). Then we add it all up to get our new stat called Grade. Taking Qay as an example, she gets a Grade of 81,077,520,321,076 (as seen in the example Table here), which is exactly 1 greater than Pat.



After we create a grade stat for each salesman, all we need do is rank the Grades by using function RANK.EQ. Admittedly, the numbers look ridiculously large, but the helper columns can be hidden or can be formatted in scientific notation. All that really matters is the ranking; this whole process will rank our salesmen according to our cascading hierarchy of categories.

[Note that Excel has a precision of only fourteen digits. So our Orders of Magnitude must add up to 14 or less, however many categories of tie-breaker are used.]

After we sort our Table (Auto_Sales) by smallest-to-largest Grade, we will use the data to write a fat cheque to our champ Qay and paltry ones to the losers.