I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.
Do not use Excel's © worksheet function RANK if you need greater flexibility. RANK cannot deal with strings, it does not provide more than one sort level and it is not able to provide unique ranks: sometimes (for sorting purposes, for example) you need different ranks for all keys. Even identical keys need to get different ranks (first occurring highest, for example).
Use COUNTIF instead:
Caveat: On its limited solution space RANK is about four to five times faster than COUNTIF as shown below.
If you are interested in downloading a 102 KB Excel 2010 © sample file which contains the functions shown here, go to my Download page, please.
Largest Remainder, Countif and COUNTIF show you other examples to use COUNTIF. A more complex application which asks for SUMPRODUCT instead of COUNTIF (because there is more than one sort level) is presented here.
Another example: If you need the sum of all ranks to stay constant (equal to n * (n+1) / 2 which RANK will only return if all data is unique):
This example you will also find in the file mentioned above.
A rank without gaps you can construct with sbUniq.
[Sulprobil] [Get it done] [Organisation] [IT] [Controlling] [Human Resources] [Family] [Contact] [Download] [Disclaimer]