Ranking with Tiebreakers

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...

Click to expand...

Read more about this Excel article...

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.

Click to expand...

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.

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.

When I want a tiebreaker, I add a column numbered 1,2,3,4,5 down the page, and I add that number divided by a large number (eg 1000000) to the number (or text) I am ranking, and rank on the result. This always breaks ties.