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:
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.
Hi Yahia! Thanks for sharing! We definitely need to create a proper more advanced course on how to use Excel at work.
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.
Submit an answer
