Algorithm Used for QUARTILE() Function

Article translations Article translations
Article ID: 103493 - View products that this article applies to.
This article was previously published under Q103493
Expand all | Collapse all

On This Page

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())
NOTE: In Microsoft Excel versions 5.0 and later, you can use the Function Wizard to insert the QUARTILE() function, by clicking Function on the Insert menu. The Function Wizard gives you information about the function, as well as required and optional arguments.

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<BR/>
          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<BR/>
          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

Properties

Article ID: 103493 - Last Review: August 15, 2003 - Revision: 1.1
APPLIES TO
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 98 for Macintosh
Keywords: 
KB103493
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com