XL: PERCENTILE Function Returns Incorrect Results

Last reviewed: February 2, 1998
Article 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, 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 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.

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 Ai

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 kth 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. We are researching this problem and will post more information here in the Microsoft Knowledge Base as it becomes available.

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, hold down the CTRL key while pressing ENTER (Windows) or 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 : xlformula
Version : WINDOWS:4.0,4.0a,5.0,5.0c; MACINTOSH:4.0,5.0
Platform : MACINTOSH WINDOWS
Issue type : kbprb

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.