XL: Algorithm Used for QUARTILE() Function

• Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, the QUARTILE() function returns a specified quartile in an array of numeric values. QUARTILE() accepts 2 arguments: Array and Quart. Array is the range of values for which you want to find the quartile value. Quart indicates the value you want to return, where:

  0   Minimum value (Same as MIN())
  1   1st quartile - 25th percentile
  2   2nd quartile - 50th percentile (Same as MEDIAN())
  3   3rd quartile - 75th percentile
  4   4th quartile - 100th percentile (Same as MAX())

MORE INFORMATION

Following is the algorithm used to calculate QUARTILE():

1. Find the kth smallest member in the array of values, where:

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

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

       quart = value between 0 and 4 depending on which quartile
               you want to find.
   
       n     = number of values in the array
   
   

2. Find the smallest data point in the array of values that is greater than the kth smallest, the (k+1)th smallest member.

3. Interpolate between the kth smallest and the (k+1)th smallest values:

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

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

Example

To find the 3rd quartile in the array of values, 0,2,3,5,6,8,9, follow these steps:

1. Find k and f:

         k=TRUNC((3/4*(7-1))+1)=5
         f=(3/4*(7-1))-TRUNC(3/4*(7-1))=.5
   

2. The 5th (kth) smallest value is 6, and the (5+1)th smallest value is 8.

3. Interpolate:

         6+(.5*(8-6))=7
   

REFERENCES

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