# Reproducing a ranking function that behaves like Excel's RANK

I tried to find a function or expression in Mathematica that produces the same output as the RANK function in Excel (see its description here), but unfortunately I could not find an existing one.

For example consider the following list:

{29400., 28200., 22300., 20900., 20300., 19800., 17400., 16600., 16300., 16100., 15500., 15300., 15300., 15200., 15100., 14900.,14700., 14700., 14400., 13900.}


The RANK function in Excel will produce:

{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 14, 15, 16, 17, 17, 19, 20}
(* *)               (* *)


Notice that ties are given the same rank and an appropriate number of ranks is skipped after that. I would like to reproduce that behavior.

In Mathematica, I used the following expression :

m = q /. Thread[# -> Ordering[#, All, Greater]] & @ Union@q


However, the output is different:

{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 13, 14, 15, 16, 16, 17, 18, 18, 19, 20}


Any suggestions on how to implement the desired behavior?

-
I have to go now but after first look it seems that you are reimplementing ArrayComponents. – Kuba Feb 25 at 11:59
@Kuba, what OP wants is almost the same as ArrayComponents, but subtly different – JasonB Feb 25 at 12:31
Welcome to Mathematica.SE! I suggest the following: 0) Browse the common pitfalls question 1) As you receive help, try to give it too, by answering questions in your area of expertise. 2) Read the faq! 3) When you see good questions and answers, vote them up by clicking the gray triangles, because the credibility of the system is based on the reputation gained by users sharing their knowledge. Also, please remember to accept the answer, if any, that solves your problem, by clicking the checkmark sign! – Dr. belisarius Feb 25 at 13:12

arr = {29400., 28200., 22300., 20900., 20300., 19800., 17400., 16600.,
16300., 16100., 15500., 15300., 15300., 15200., 15100., 14900.,
14700., 14700., 14400., 13900.}


From here

RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).

# /. Thread[Reverse@Sort@# -> Range[Length@#] ] &@arr


{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 14, 15, 16, 17, 17, 19, 20}

-
This is nice and simple, you should get the cake – JasonB Feb 25 at 13:53
Great use of ReplaceAll's first rule behaviour. Was totally out of the box for me. – Edmund Feb 25 at 19:36

It feels clunky, but this gets you there,

q /. (Thread[# ->
First@First@Position[Reverse@Sort@q, #]] & /@ q)
(* {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 14, 15, 16, 17, 17, 19, 20} *)

-
f[1, _] = 1;
f[n_, l1_] := If[l1[[n]] == l1[[n - 1]], f[n - 1, l1], n]
f[#, Sort[-l]] & /@ Range@Length@l

(* {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 14, 15, 16, 17, 17, 19, 20}*)

-
Apparently OP wants ties to have the same rank. – J. M. Feb 25 at 12:10
@J.M. Thanks. I've misread. Posted a different approach now. – Dr. belisarius Feb 25 at 13:02
dat = {29400., 28200., 22300., 20900., 20300., 19800., 17400., 16600., 16300.,
16100., 15500., 15300., 15300., 15200., 15100., 14900., 14700., 14700., 14400.,
13900.};


dat /. First /@ PositionIndex @ Reverse @ Sort @ dat

{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 14, 15, 16, 17, 17, 19, 20}


However for best performance consider using cleanPosIdx from Why is the new PositionIndex horribly slow?

Also this question is closely related to Ordering function with recognition of duplicates. Using myOrdering as a foundation:

Min[#] 1^# & /@ myOrdering[dat] // Reverse // Catenate

{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 12, 14, 15, 16, 17, 17, 19, 20}

-