Microsoft Office Tutorials and References

In Depth Information

**Using RANK to Calculate the Position Within a List**

More

More Than

Than You

You Ever

Ever Wanted

Wanted tto Know

Know About

About the

the Controversy

Controversy oof Per-

Per-

centiles and Quartiles

centiles and Quartiles

A huge argument is raging over the best way to calculate percentiles

and quartiles. Suppose that you have 11 scores. You ask Excel to

tell you the score at the 15th percentile. Typically, Excel assigns

the smallest value to the 0th percentile and the largest value to the

100th percentile. Because there are 10 steps between the smallest and

largest value, the 10th percentile will be the second-smallest num-

ber, and the 20th percentile will be the third-smallest number. What

number is at the 15th percentile? How about at the 17th percentile? Ex-

cel divides the gap between the second and third values into 10 equal

parts and uses that interpolation to calculate the number at the 11th

percentile and so on.

In 1996, two scholars named Hyndman and Fan published a paper de-

tailing 12 different methods for calculating percentiles and quart-

iles. Legacy versions of Excel used method #7, which was defined by

Gumbull. Other software such as MiniTab and SPSS used method #6,

which was defined by Weibull. People who care a lot about percent-

iles and quartiles talk about
“
Hyndman and Fan Method #X.
”

In Excel, the Hyndman and Fan method #7 is still available as

QUARTILE.INC, PERCENTILE.INC, and PERCENTRANK.INC. Excel 2010

introduced support for Hyndman and Fan method #6 as QUARTILE.EXC,

PERCENTILE.EXC, and PERCENTRANK.EXC.

Here is what you have to know about the controversy: With a data set

of 100 numbers or more, the difference between .EXC and .INC versions

are small
—
less than one-half of 1%. However, in small data sets

of n=4 to n=7, the values at the first quartile can swing by 40%. For

example, in the data set of {10,22,33,40}, Weibull calculates the first

quartile at 13 and Gumbull calculates the first quartile at 19. The

delta between 19 and 13 is 6, which is 46% of 13.

Also, in the .EXC version, there is no 0th percentile and no 100th per-

centile. The .EXC stands for percentiles from 0% to 100%, exclusive.

The .INC stands for percentiles from 0% to 100% inclusive.