29 Feb 2024

Posted on:

10 Feb 2023

2

# Resolved:Rank function must be used with countif

Greetings,

Rank function must be used with countif function otherwise you will get inaccurate rankings (when 2 companies or more have the same revenue for example)

Here is a good example for proper usage of rank function with countif:

in the below snapshot a sort of 7 companies are ranked by their revenue using RANK function only. Notice, 2 companies B and C were ranked 5th because they have the same revenue.

To overcome this, we have to count the occurrence for each revenue and add it to the rank function (and deduct 1 from the total). Notice, now the ranking for the 2 companies B and C are fixed.

See the below sanpshot to clarify this idea:

Posted on:

10 Feb 2023

5

I know that the usage for this combination of functions may be beyond the scope of this course, however I believe it is worth knowing. Thank you for the great content of this course.

Instructor
Posted on:

10 Feb 2023

2

Hi Yahia! Thanks for sharing! We definitely need to create a proper more advanced course on how to use Excel at work.

Posted on:

29 Feb 2024

0

I actually prefer that companies be assigned the same rank if they have identical revenues. It seems fair to me. However I do appreciate sharing your solution.

My only issue concerns the current ranking method.

I would prefer the rankings to follow a sequence like 1, 2, 3, 4, 5, 5, 6, rather than 1, 2, 3, 4, 5, 5, 7.