PERCENTRANK function

[Scott]

Hi

What is the algorithm underlying the PERCENTRANK function?

 

[Vasant Nanavati]

In theory, if one is to believe Help:

=PERCENTRANK($A$1:$A$10,$A1)

should be equivalent to:

=COUNTIF($A$1:$A$10,"<"&$A1)/COUNTIF($A$1:$A$10,"<>"&$A1)

but they don't seem to give exactly the same results when a value is
duplicated in the array. So either my understanding is incorrect or Help is
wrong.

 

[Michael R Middleton]

Scott -

What is the algorithm underlying the PERCENTRANK function? <

See Help for PERCENTILE for some hints.



Also, there's a possible relevant Knowledge Base article:



129436 - PERCENTRANK() May Appear to Return Incorrect Results



And some perhaps-related information in this article:



103493 - Algorithm Used for QUARTILE() Function



- Mike Middleton, www.usfca.edu/~middleton

 

[Leo Heuser]

Hi Scott

This one seems to work:

=PERCENTRANK($A$1:$A$20,B1)

equals

(1/(ROWS($A$1:$A$20)-1))*(ROWS($A$1:$A$20)-RANK(B1,$A$1:$A$20))
which equals
1 - (RANK(B1,$A$1:$A$20)-1)/(ROWS($A$1:$A$20)-1)

Or if N is the amount of numbers (here 20):

1 - (RANK(B1,$A$1:$A$20)-1)/(N-1)

or in plain text:

1 - (RankOfNumber-1)/(AmountOfNumbers-1)

Finally it looks as if PERCENTRANK() rounds down to 3 decimals, so

ROUNDDOWN(1 - (RankOfNumber-1)/(AmountOfNumbers-1),3)