XL: PERCENTILE Function Returns Incorrect Results

ID: Q99963

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)<k<i/(n-1), return:

         (k-(i-1)/(n-1))*(A(i)+1-A(i))
     A(i)+ -------------------------
                  1/(n-1)

     If k=(i-1)/(n-1), return A(i)

PERCENTILE() 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 k-th percentile may be erroneous. When k>=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)).

REFERENCES

"Function Reference," page 325

Additional query words: percentrank

Keywords : kbdta xlformula
Version : WINDOWS:4.0,4.0a,5.0,5.0c; MACINTOSH:4.0,5.0
Platform : MACINTOSH WINDOWS
Issue type : kbprb