# Microsoft Knowledge Base

## Excel: QUARTILE() May Return Incorrect Results

Last reviewed: September 12, 1996
Article ID: Q103497

• 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

1. Enter the following into a worksheet:

A1: 1 A2: 1 A3: 1 A4: 5

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

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