XL: PERCENTILE Function Returns Incorrect ResultsLast reviewed: February 2, 1998Article ID: Q99963 |
The information in this article applies to:
SYMPTOMSThe PERCENTILE() function, new to Microsoft Excel version 4.0, is used to return a value within a range at the percentile you specify. The following examples will show the formula used to calculate PERCENTILE() and describe a scenario in which PERCENTILE() may not return the correct result.
MORE INFORMATIONPERCENTILE() takes two arguments, array and k. The array is a range of n numbers, A1,...,An. The smallest value in the array corresponds to the 0th percentile and the highest value corresponds to the 100th percentile with values in between at the i/(n-1)th percentile. k is the percentile for which you want to find the corresponding value. To calculate the value for any given percentile (k) where 0<=k<=1:
If (i-1)/(n-1)<k<i/(n-1), return: (k-(i-1)/(n-1))*(Ai+1-Ai) Ai+ ------------------------- 1/(n-1) If k=(i-1)/(n-1), return AiPERCENTILE() may not return the correct result when there is more than one occurrence of the lowest values or highest values. When this occurs, it may cause the following condition to be true:
k<1/(n-1)For every instance where this condition is true, the value returned for the kth percentile may be erroneous. When k>=1/(n-1), the results will be correct.
STATUSMicrosoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post more information here in the Microsoft Knowledge Base as it becomes available.
ExampleTo see an example of this problem, follow these steps:
REFERENCES"Function Reference," page 325
|
Additional query words: percentrank
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |