Article 33 – Rank when Duplicates Exist (Ties)

Ranking when duplicates (ties) exist is an interesting problem and you will be called upon to make choices when duplicates exist and you have to rank them. Suppose, you have the data like below. 2 rows are formatted in Yellow and 3 rows are formatted in Green to demonstrate the existence of duplicates.

For the purpose of this article, Descending Rank has been considered. Hence, highest value has lowest rank of 1.

<< The workbook related to this article can be downloaded from Article 33 – Rank when Duplicates Exist >>>

APPROACH 1 – RANK Function

When we use regular RANK formula

=RANK(B2,$B$2:$B$10)

We get following results

We notice that both Yellows are given a rank of 3 as one would have 3 and one would have 4. We also notice, excel didn't allocate rank 4 to anything as both Yellows consumed ratking 4 also.

We notice that 3 Greens are given a rank of 6. Hence, they consumed ranks 6, 7 and 8. Hence, next rank allocated is 9. Ranks 7 and 8 are not allocated to anything.

This may not be acceptable to you or your organization.

Approach 2 – RANK.EQ Function

RANK.EQ works like RANK function. In case of ties, it gives Top Rank to values. Hence, if you have 4th rank for 3 values, all 3 values will have a rank of 4 as 4 is the top rank. RANK.EQ is simply a replacement for RANK function and RANK.EQ should be used in place of simple RANK as this provided improved accuracy over RANK as per Excel help.

Put following formula

=RANK.EQ(B2,$B$2:$B$10)

We get the same results as in RANK function. This may not be acceptable to you or your organization.

Approach 3 – RANK.AVG Function

We move over to RANK.AVG function. RANK.AVG gives same rank to duplicates like a rank function but it averages and may give decimal ranks to duplicates. Hence, if you have 4th rank for 3 values, it will average 4, 5 and 6 i.e. (4+5+6)/3 = 5, hence will give the rank of 5 for all 3.

Put following formula

=RANK.AVG(B2,$B$2:$B$10)

We get following results –

Lisa and Christian are ranked 3. Hence, average of 3 and 4 = 3.5 will be the rank given to them. Rank 4 will not be allocated to anyone as it has been consumed. Susan, Dove and Waterman have rank of 6. Hence average of 6,7 and 8 = 7 will be the rank given to 3 of them. Next rank, allocated will be 9 and ranks upto 8 have already been consumed.

This makes it confusing. But this may be acceptable to you or your organization.

Approach 4 – COUNTIF

You or your organization wants that distinct ranks should be given to all of them and in case of ties, first entry should have higher rank than 2nd entry and so on.

COUNTIF comes to the rescue here. Enter following formula –

=RANK(B2,$B$2:$B$10)+COUNTIF(B$2:B2,B2)-1

And you get following results –

You will notice that all ranks from 1 to 9 have been given and none of the ranks are skipped. An entry appearing first has higher rank in case of ties.

Approach 5 – SUMPRODUCT

You are still not satisfied. You want that in case of ties, ranks should be same for all ties. But next number should not skip the rank. Hence, if Lisa and Christian have a rank of 3, Smith should be given a rank of 4 not of 5.

You should enter following formula –

=SUMPRODUCT(–(B2 < B$2:B$10),1/COUNTIF(B$2:B$10,B$2:B$10))+1

And you get the desired result –

It depends upon you which approach to choose.