Microsoft Knowledge Base

Excel: QUARTILE() May Return Incorrect Results

Last reviewed: September 12, 1996
Article ID: Q103497

The information in this article applies to:

Microsoft Excel for Windows, versions 4.0, 4.0a
Microsoft Excel for the Macintosh, version 4.0

SYMPTOM

In Microsoft Excel, when you use the QUARTILE() function to find the 1st quartile (25th percentile) of a range of values in which the minimum value is duplicated or the 3rd quartile (75th percentile) of a range in which the maximum value is duplicated, the result may be incorrect.

RESOLUTION

The algorithm used to calculate the QUARTILE() function will return the correct result. The following provides an example in which QUARTILE() returns the incorrect result and explains how to get the correct result using the algorithm.

Example

Enter the following into a worksheet:

A1: 1 A2: 1 A3: 1 A4: 5
If you use the QUARTILE() function to find the 1st quartile as follows:
```
      =QUARTILE(A1:A4,1)
```
This formula will return the value 4. The correct result is 1.

To calculate the correct result:

a. Find the kth smallest value in A1:A4, where

          k=(quart/4)*(n-1))+1

          k=(1/4)*(4-1))+1=1.75

          If k is not an integer, truncate it but store the
          fractional portion (f) for use in step 3.

          f=((1/4)*(4-1)+1)-TRUNC((1/4*(4-1)+1)=.75

       quart = value between 0 and 4 depending on which quartile
               you want to find.

       n     = number of values in the array

   b.  Using the 1st (kth) smallest and 2nd (k+1)th smallest,
       interpolate between the two values:

          quartile = a[k]+(f*(a[k+1]-a[k]))

          a[k]   = the kth smallest

          a[k+1] = the k+1th smallest

          quartile = 1+(.75*(1-1))=1

STATUS

Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed above. This problem does not occur in later versions.

REFERENCES

"Function Reference," version 4.0, pages 342-343

KBCategory: kbusage

KBSubcategory:
Additional reference words: 4.00 4.00a PERCENTILE()

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.

Microsoft KB Archive/103497

Contents