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

## REFERENCES

"Function Reference," version 4.0, pages 342-343

KBCategory: kbusage Last reviewed: September 12, 1996 |