Microsoft KB Archive/99963

{|
 * width="100%"|

XL: PERCENTILE Function Returns Incorrect Results

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c
 * Microsoft Excel for the Macintosh, versions 4.0, 5.0
 * Microsoft Excel for Windows NT, version 5.0

-

SYMPTOMS
The PERCENTILE function, new to Microsoft Excel version 4.0, returns a value within a range at the percentile you specify. The following examples show the formula used to calculate PERCENTILE and describe a scenario in which PERCENTILE may not return the correct result.

MORE INFORMATION
PERCENTILE 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. i is the subscript denoting the i-th element of the array.

To calculate the value for any given percentile (k) where 0<=k<=1:

    If (i-1)/(n-1)=1/(n-1), the results will be correct.

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

Example
To see an example of this problem, follow these steps:

  Enter the data from cells A1:B11 into a worksheet:

     A1: 1   B1: 0 A2: 1  B2: .1 A3: 1  B3: .2 A4: 2  B4: .3 A5: 4  B5: .4 A6:    B6: .5 A7:    B7: .6 A8:    B8: .7 A9:    B9: .8 A10:   B10: .9 A11:   B11: 1  Select cells C1:C11 and type the following formula:

=PERCENTILE($A$1:$A$5,B1) To enter the formula, press CTRL+ENTER (Windows) or CONTROL+RETURN (Macintosh). This simultaneously enters the formula into all selected cells.

Cells C1:C11 contain the results of the PERCENTILE function. Cells C2:C3 are incorrect as both should be a value of 1. Both k values, .1 and .2, are less than .25, (1-(n-1)).