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
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.
Search JabSto ::

Custom Search