Calculating percentiles with Microsoft Excel

Abstract

This article shows how to calculate percentiles with Microsoft Excel using the following functions:

PERCENTILE.EXC
PERCENTILE.INC
PERCENTRANK.EXC
PERCENTRANK.INC
PERCENTILE (legacy function)
PERCENTRANK (legacy function)

Introduction

"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

=(I21/100)*($I$27-1)+1

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:

=SVERWEIS(K22;$A$2:$E$12;5;0)+L22*(SVERWEIS(K22+1;$A$2:$E$12;5;0)-SVERWEIS(K22;$A$2:$E$12;5;0))

This is calculated according to the second variant


Live preview


Area chart example with percentiles

Conditional Formatting

Another way to use percentiles in Excel is the conditional formatting with icons sets.

Percentrank

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.



Dieter Neumann