• If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk.
    If you have any questions regarding an article, please use the Article Discussion section.
DRSteele

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 is never greater than 1000 contracts. Similarly, for Sports Packages the longest length is 4, meaning that for this data Sports Packages is never greater than $10,000. Similarly, for Extended Warranties the longest length is 5, meaning that for this data Extended Warranties is never greater than $100,000. Lastly, for Sales Level the longest length is 2, meaning that for this data Sales Level is never greater than 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.

Book1
NO
1Rank OrderSalesman Qay
2SALES LEVEL
3Extended Warranties
4Sport Packages
5Rustproofing Contracts
6GRADE
Auto_Sales


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.

Book1
ABCDEFGHIJK
2orders of magnitude:
3magnitude (base 10):
4mutiplier1
5
6
7
8SalesmanSales LevelExtended WarrantiesSport PackagesRustproofing ContractsSales Level GradeExtended Warranty GradeSport Packages GradeRustproofing Contracts GradeGRADEGRADE RANK
9Qay81$7,752$32176
10Pat81$7,752$32175
11Roy81$7,752$297100
12Abe81$5,304$1,12097
13Les81$5,304$1,12096
14Joy81$1,029$1087
15Bob81$306$065
16Una78$2,448$54268
17Don78$2,448$5420
18Eve78$2,346$54240
19Ida78$2,346$54232
20Tom78$2,043$68789
21Gia78$0$250
22Ken63$1,734$1102
23Vic63$1,734$0102
24Mia63$1,734$088
25Yul63$516$12833
26Sue63$204$12833
27Hal63$102$12833
28Ole43$0$1841
29Ned32$0$1812
30Zeb21$10,200$1711
31Fay10$816$6232
32Wes10$816$622
33Xyr10$102$201100
34Cal10$0$2010
Auto_Sales
Cell Formulas
Range(s)Formula
B2B2=MAX(LEN(Auto_Sales[Sales Level]))
C2C2=MAX(LEN(Auto_Sales[Extended Warranties]))
D2D2=MAX(LEN(Auto_Sales[Sport Packages]))
E2E2=MAX(LEN(Auto_Sales[Rustproofing Contracts]))
B3:E3B3=10^B2
B4B4=10^(SUM(C2:E2))
C4C4=10^(SUM(D2:E2))
D4D4=10^(SUM(E2))
F9:H34F9=B9*B$4
I9:I34I9=[@[Rustproofing Contracts]]
J9:J34J9=SUM(Auto_Sales[@[Sales Level Grade]:[Rustproofing Contracts Grade]])
K9:K34K9=RANK.EQ([@GRADE],[GRADE])
Excel Version
365
Author
DRSteele
Views
114
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from DRSteele

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top