• 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
81,000,000,000,000
3Extended Warranties
77,520,000,000
4Sport Packages
$321,000
5Rustproofing Contracts
76
6GRADE
81,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:
2
5
4
3
3magnitude (base 10):
100
100,000
10,000
1,000
4mutiplier
1,000,000,000,000
10,000,000
1,000
1
5
6
7
8SalesmanSales LevelExtended WarrantiesSport PackagesRustproofing ContractsSales Level GradeExtended Warranty GradeSport Packages GradeRustproofing Contracts GradeGRADEGRADE RANK
9Qay81$7,752$32176
81,000,000,000,000
77,520,000,000
321,000
76
81,077,520,321,076
1
10Pat81$7,752$32175
81,000,000,000,000
77,520,000,000
321,000
75
81,077,520,321,075
2
11Roy81$7,752$297100
81,000,000,000,000
77,520,000,000
297,000
100
81,077,520,297,100
3
12Abe81$5,304$1,12097
81,000,000,000,000
53,040,000,000
1,120,000
97
81,053,041,120,097
4
13Les81$5,304$1,12096
81,000,000,000,000
53,040,000,000
1,120,000
96
81,053,041,120,096
5
14Joy81$1,029$1087
81,000,000,000,000
10,290,000,000
10,000
87
81,010,290,010,087
6
15Bob81$306$065
81,000,000,000,000
3,060,000,000
0
65
81,003,060,000,065
7
16Una78$2,448$54268
78,000,000,000,000
24,480,000,000
542,000
68
78,024,480,542,068
8
17Don78$2,448$5420
78,000,000,000,000
24,480,000,000
542,000
0
78,024,480,542,000
9
18Eve78$2,346$54240
78,000,000,000,000
23,460,000,000
542,000
40
78,023,460,542,040
10
19Ida78$2,346$54232
78,000,000,000,000
23,460,000,000
542,000
32
78,023,460,542,032
11
20Tom78$2,043$68789
78,000,000,000,000
20,430,000,000
687,000
89
78,020,430,687,089
12
21Gia78$0$250
78,000,000,000,000
0
25,000
0
78,000,000,025,000
13
22Ken63$1,734$1102
63,000,000,000,000
17,340,000,000
1,000
102
63,017,340,001,102
14
23Vic63$1,734$0102
63,000,000,000,000
17,340,000,000
0
102
63,017,340,000,102
15
24Mia63$1,734$088
63,000,000,000,000
17,340,000,000
0
88
63,017,340,000,088
16
25Yul63$516$12833
63,000,000,000,000
5,160,000,000
128,000
33
63,005,160,128,033
17
26Sue63$204$12833
63,000,000,000,000
2,040,000,000
128,000
33
63,002,040,128,033
18
27Hal63$102$12833
63,000,000,000,000
1,020,000,000
128,000
33
63,001,020,128,033
19
28Ole43$0$1841
43,000,000,000,000
0
18,000
41
43,000,000,018,041
20
29Ned32$0$1812
32,000,000,000,000
0
18,000
12
32,000,000,018,012
21
30Zeb21$10,200$1711
21,000,000,000,000
102,000,000,000
17,000
11
21,102,000,017,011
22
31Fay10$816$6232
10,000,000,000,000
8,160,000,000
62,000
32
10,008,160,062,032
23
32Wes10$816$622
10,000,000,000,000
8,160,000,000
62,000
2
10,008,160,062,002
24
33Xyr10$102$201100
10,000,000,000,000
1,020,000,000
201,000
100
10,001,020,201,100
25
34Cal10$0$2010
10,000,000,000,000
0
201,000
0
10,000,000,201,000
26
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
474
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