Microsoft KB Archive/103497

= Microsoft Knowledge Base =

Excel: QUARTILE May Return Incorrect Results
Last reviewed: September 12, 1996

Article ID: Q103497

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 4.0a
 * Microsoft Excel for the Macintosh, version 4.0

SYMPTOM
In Microsoft Excel, when you use the QUARTILE function to find the 1st quartile (25th percentile) of a range of values in which the minimum value is duplicated or the 3rd quartile (75th percentile) of a range in which the maximum value is duplicated, the result may be incorrect.

RESOLUTION
The algorithm used to calculate the QUARTILE function will return the correct result. The following provides an example in which QUARTILE returns the incorrect result and explains how to get the correct result using the algorithm.

Example
  Enter the following into a worksheet: A1: 1 A2: 1 A3: 1 A4: 5   If you use the QUARTILE function to find the 1st quartile as follows: =QUARTILE(A1:A4,1) This formula will return the value 4. The correct result is 1.   To calculate the correct result: a. Find the kth smallest value in A1:A4, where k=(quart/4)*(n-1))+1

k=(1/4)*(4-1))+1=1.75

If k is not an integer, truncate it but store the fractional portion (f) for use in step 3.

f=((1/4)*(4-1)+1)-TRUNC((1/4*(4-1)+1)=.75

quart = value between 0 and 4 depending on which quartile you want to find.

n    = number of values in the array

b. Using the 1st (kth) smallest and 2nd (k+1)th smallest, interpolate between the two values:

quartile = a[k]+(f*(a[k+1]-a[k]))

a[k]  = the kth smallest

a[k+1] = the k+1th smallest

quartile = 1+(.75*(1-1))=1 

STATUS
Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed above. This problem does not occur in later versions.