• 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 LEVEL81,000,000,000,000
3Extended Warranties77,520,000,000
4Sport Packages$321,000
5Rustproofing Contracts76
6GRADE81,077,520,321,076
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:2543
3magnitude (base 10):100100,00010,0001,000
4mutiplier1,000,000,000,00010,000,0001,0001
5
6
7
8SalesmanSales LevelExtended WarrantiesSport PackagesRustproofing ContractsSales Level GradeExtended Warranty GradeSport Packages GradeRustproofing Contracts GradeGRADEGRADE RANK
9Qay81$7,752$3217681,000,000,000,00077,520,000,000321,0007681,077,520,321,0761
10Pat81$7,752$3217581,000,000,000,00077,520,000,000321,0007581,077,520,321,0752
11Roy81$7,752$29710081,000,000,000,00077,520,000,000297,00010081,077,520,297,1003
12Abe81$5,304$1,1209781,000,000,000,00053,040,000,0001,120,0009781,053,041,120,0974
13Les81$5,304$1,1209681,000,000,000,00053,040,000,0001,120,0009681,053,041,120,0965
14Joy81$1,029$108781,000,000,000,00010,290,000,00010,0008781,010,290,010,0876
15Bob81$306$06581,000,000,000,0003,060,000,00006581,003,060,000,0657
16Una78$2,448$5426878,000,000,000,00024,480,000,000542,0006878,024,480,542,0688
17Don78$2,448$542078,000,000,000,00024,480,000,000542,000078,024,480,542,0009
18Eve78$2,346$5424078,000,000,000,00023,460,000,000542,0004078,023,460,542,04010
19Ida78$2,346$5423278,000,000,000,00023,460,000,000542,0003278,023,460,542,03211
20Tom78$2,043$6878978,000,000,000,00020,430,000,000687,0008978,020,430,687,08912
21Gia78$0$25078,000,000,000,000025,000078,000,000,025,00013
22Ken63$1,734$110263,000,000,000,00017,340,000,0001,00010263,017,340,001,10214
23Vic63$1,734$010263,000,000,000,00017,340,000,000010263,017,340,000,10215
24Mia63$1,734$08863,000,000,000,00017,340,000,00008863,017,340,000,08816
25Yul63$516$1283363,000,000,000,0005,160,000,000128,0003363,005,160,128,03317
26Sue63$204$1283363,000,000,000,0002,040,000,000128,0003363,002,040,128,03318
27Hal63$102$1283363,000,000,000,0001,020,000,000128,0003363,001,020,128,03319
28Ole43$0$184143,000,000,000,000018,0004143,000,000,018,04120
29Ned32$0$181232,000,000,000,000018,0001232,000,000,018,01221
30Zeb21$10,200$171121,000,000,000,000102,000,000,00017,0001121,102,000,017,01122
31Fay10$816$623210,000,000,000,0008,160,000,00062,0003210,008,160,062,03223
32Wes10$816$62210,000,000,000,0008,160,000,00062,000210,008,160,062,00224
33Xyr10$102$20110010,000,000,000,0001,020,000,000201,00010010,001,020,201,10025
34Cal10$0$201010,000,000,000,0000201,000010,000,000,201,00026
Auto_Sales
Cell Formulas
RangeFormula
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
432
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

Top