How do you rank in Excel without ties?

Answer 1

I’m guessing you’d like to rank data that might have ties in such a way that the ranks have no ties. In other words, if your data were “1,7,9,7”, you’d like the ranks to be “1, 2, 4, 3”, rather than “1, 2, 3, 2”.

Lets assume the numbers are in column A, and there are 100 of them.

1) Enter this formula into B1

=RANK(a1, a$1:a$100,1)+COUNTIF(a$1:a1,a1)-1

2) select B1, copy, and spread it down to B100

This will produce a rank in B for each cell in A. If there are ties, they will both get the same rank, but the COUNTIF will adjust the ties.

It’s an easy answer, but NOT obvious.

First, here’s what RANK did for you: when it found there are duplicates (let’s say the highest five numbers were unique, but the next three were equal), it gives ranks 1-5 to the top five, then gives 6 to the triplets, and it makes room, giving rank 9 to the next highest number in the entire table.

Now our COUNTIF is going to add 1 to the first triplet, 2 to the second triplet, and 3 to the third triplet. Then we subtract 1, because we’ve got 1 too many.

But there’s some hidden magic, the keystone of Excel formulas, the automatic renumbering.

When the formula has been copied into all 100 rows, it looks different because of some automatic numbering. In particular, in any row (let’s say row 37), we want the COUNTIF to count matches up to and including “this” row (for example 37), but not beyond this row., So we want the range to be 1 through 37. This is done by making a range that starts in absolute 1 and goes to “this” row (37). In the formula, this is written as a$1:a1.

The “a$1” means “start at row 1, regardless of where this is copied”, while the next “a1” means “up to this row”