微软知识库中的介绍,对计算原理进行了分析。
XL2000: Algorithm Used for QUARTILE() Function
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())
MORE INFORMATION
The 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.
And where:
o
o quart = value between 0 and 4 depending on which quartile you want to find
o 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 third 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 fifth (kth) smallest value is 6, and the (5+1)th smallest value is 8.
3. Interpolate:
6+(.5*(8-6))=7
The information in this article applies to
Microsoft Excel 2000
[此贴子已经被作者于2004-11-4 2:19:37编辑过] |