XL2000: Algorithm Used for QUARTILE() Function

View products that this article applies to.

This article was previously published under Q214072

SUMMARY

In Microsoft Excel 2000, the QUARTILE()function 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())

Back to the top

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

Back to the top