How do you rank a tie in Excel?

Answer 1

Using the Excel RANK function, any ties are both given the same value. Some definitions of rank would prefer that the two share an average rank (using the assigned rank and 1 above it) instead of the same rank, like 5.5. Here's an example from the Office Support website:

https://support.office.com/en-us/article/RANK-function-6A2FC49D-1831-4A03-9D8C-C279CF99F723

There is a formula present in the webpage that offers a work-around if you require average ranks:

=[COUNT(ref) + 1 - RANK(number, ref, 0) - RANK(number, ref, 1)]/2

In the case of ties, no value gets the upper rank (in the example, 6), so you'll have ranks of 1, 2, 3, 4, 5, 5, 7. In the alternative definition, you would have 1, 2, 3, 4, 5.5, 5.5, 7, so there is still no 6.

If you really want all numbers to appear uniquely, you could assign a rank using the RANK function or the alternative definition, sort the rows according to rank, and then assign a row number to each, where the first row =1 and the next rows are +1 of the prior row. This isn't really a rank any longer, but a sorted and numbered rank, and the ties will be arbitrarily sorted. As a result of arbitrary sorting, there is no guarantee that they will appear in the same order and receive the same row number after refreshing the data and / or sorting again.

In the end, it's best to simply use the RANK function or the alternative and accept the fact that ties will appear as ties in the output number. So it's possible that certain integers may not appear at all in the final rank, and this aligns with the real-life definition of a rank (e.g., a race with a tie).