This article shows how to calculate percentiles with Microsoft Excel using the following functions:
PERCENTILE (legacy function)
PERCENTRANK (legacy function)
"A percentile (or a centile) is a measure used in statistics indicating the value below which a
given percentage of observations in a group of observations fall. For example, the 20th percentile
is the value (or score) below which 20% of the observations may be found.
[...]The 25th percentile is also known as the first quartile (Q1), the 50th percentile as the median
or second quartile (Q2), and the 75th percentile as the third quartile (Q3). In general, percentiles
and quartiles are specific types of quantiles."1
Very often the interquartile range (IQR)
is shown representing the range between the first and the
third quartile or in other words the range between the 25th and 75th percentile.
Topics of interest
The PERCENTILE function is the legacy version of PERCENTILE.INC which is available as of Excel 2007.
Excel's conditional formatting uses the same mathematics as the PERCENTILE.INC function.
For calculating the percentile ranges manually, we have to calculate the ordinal rank in cell J21 (etc.) with
which means the portion of the total items count reduced by one plus one. Then we have to split into the
integer and into the fractional amount of the result, e.g. 7.75 splits into 7 (int) and .75 (fract).
The number to be calculated is the ordinal result of the integer part, here the 7th value of the list (5) plus
the fractional amount times the difference of the 8th value - the 7th value of the ordered list, here
6-5=1 * 0.75 = 0.75
which is represented in the formula:
This is calculated according to the second variant
Area chart example with percentiles
Another way to use percentiles in Excel is the conditional formatting with icons sets.
The PERCENTRANK-formula can be used with a given accuracy (significance). The default value is three (digits).
We can use a linear interpolation to calculate the percentile from a percentile rank.
The higher the given significant, the higher is the accuracy compared to using the percentile function.
Calculation the percent rank could help when developing micro charts based on color scales that rely on
percentile calculations. This could be a starting point for a new article.