G
Guest
Hi
What is the algorithm underlying the PERCENTRANK function?
What is the algorithm underlying the PERCENTRANK function?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
V
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.
=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.
M
Michael R Middleton
Scott -
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
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
L
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)
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)