Microsoft KB Archive/214072

= XL2000: Algorithm Used for QUARTILE Function =

Article ID: 214072

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214072





SUMMARY
In Microsoft Excel 2000, the QUARTILEfunction returns a specified quartile in an array of numeric values. QUARTILE accepts two 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
The following is the algorithm used to calculate QUARTILE:  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.

And where:  

 quart = value between 0 and 4 depending on which quartile you want to find n = number of values in the array  Find the smallest data point in the array of values that is greater than the kth smallest -- the (k+1)th smallest member.</li> 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

</li></ol>

Example
To find the third quartile in the array of values 0,2,3,5,6,8,9, follow these steps: <ol> Find k and f:

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

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

</li> The fifth (kth) smallest value is 6, and the (5+1)th smallest value is 8.</li> Interpolate:</li></ol>

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

Additional query words: xl2000 xl2k

Keywords: kbhowto kbinfo KB214072

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.