I have a list of 40 things that have scores attached to them. I need to Rank them between 1 and 40, so I thought I’d just use the Rank function.
However, some ranks are duplicated because they have the same score.
Is there some way to arbitrarily (the rank doesn’t matter too much, it’s arbitrary) choose between the two closest ranks if they are identical? For instance, if two are ranked 1, but there are no 2’s, can one be ranked 1 and the other 2?
Create a tie breaker column where you add a unique offset value smaller than the “minimum gap” between ranks. This way, even the largest offset value will not alter the sequencing. Assuming that scores/ranks are integers, the minimum gap is 1, so you need the tie breaker to be a fraction between 0 and 1.
I prefer to use the position in the list as the tie breaker. Divide row number by the count of rows in your list, and you have your tie breaker fraction. Something like …
=<score>+(ROW()/ROWS(<scorelist>))
The suggested formula assumes that the list starts from the top of the sheet. If your list does not start from row 1, you need to shift the row number or increase the divisor accordingly in the calculation. Otherwise you will get fractions >1 in the lower part of your list, which could mess up the sequence of your ranking.
Thanks. What I did, was to follow your advice. I created another column with a “Final Score” which added a random number less than 0.01 to the column number (also divided by 100) and then calculated the new Ranks. This added an arbitrary, slightly random value of about 0.02 or 0.03 to each number. Now all the 31’s that are the same are slightly different, but still lower than 32 and higher than 30.
That should do it. More compact and efficient than what I suggested.
In theory, two generated pseudorandom numbers can amount to the same value after summing/rounding. However, the probability that this occurs between tied entries is negligible. Not likely to happen in a hundred years.
The main advantage of my suggestion is with multiple ties, where order between tied entries is preserved (so if your list is alphabetized, the individuals within each tie will also be in alpha order).
Most used disambiguations when ranking are either “stable” or “antistable” regarding the original order (sequence). Random disambiguation is uncommon, and can only reliably work if you take additional measures to make sure that the random numbers you create for repeating occurrences can’t be equal.
(@keme1 and myself posted our recent comments “randomly” at nearly exactly the same time.)
To get a step further I also attach example sheets generalizing the topic a bit:
disask76107disambiguatingRankingAndSorting.ods (376.8 KB)