Wednesday, January 16, 2008

Excel Quartiles - Not so fast...

The formula that I posted for Quartiles in Excel (n+3/4) is not precise. Here's the algorithm for computing quartiles that Excel uses according to the Microsoft knowledge base:

The following is the algorithm used to calculate QUARTILE():

1. Find the kth smallest member in the array of values, where:
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
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

No comments: