Microsoft Knowledge Base

• 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

1. Enter the following into a worksheet:

   A1: 1 A2: 1 A3: 1 A4: 5

2. 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.

3. 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