DRSteele

Ranking with Tiebreakers

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,236
Office Version
365
Platform
Windows
DRSteele submitted a new Excel article:

Ranking with Tiebreakers - Using a new statsitic, we can rank performance by using cascading 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...
Read more about this Excel article...
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,657
Office Version
365
Platform
Windows
Hi Don

I have used similar concepts for ranking previously, including sometimes decimal digits, particularly for categories where a lower value actually ranks higher.

However, I think your article would be better if it included a caveat about the size or number of categories, as the following statement makes it sound like there is no limit.
Note that other data sets can have more or fewer tie-breaking categories, to be sure.
Your example results in a Grade figure with a maximum of 14 digits, conveniently just under the Excel limit for significant digits. If we take the quote above and add one more category with a magnitude of 3, then the Grade figure exceeds the significant digit limit and the ranking is no longer reliable. Another example, rather than introducing another category is to simply change Qay and Pat's Sales level to 81000 & suddenly their ranking is equal, even though they still differ in Rustproofing Contracts.
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,236
Office Version
365
Platform
Windows
Hi Peter,

Thanks for your comments. I actually have written about this before where I used the cascading categories as part of the decimal for Grade (meaning that I divided rather than multiplied the lesser categories). That too did mean a fourteen-digit precision limit. I will amend the article accordingly.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,150
Messages
5,466,970
Members
406,513
Latest member
t0ny84

This Week's Hot Topics

Top