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.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Rank

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.

20091212_PB_01_Better_Rank

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):

20100410_PB_01_Rank_average_dupes

This example you will also find in the file mentioned above.

A rank without gaps you can construct with sbUniq.

Sulprobil   Get it done   Contact   Disclaimer   Download