Leading the way in Microsoft Office Development

PERCENTILE Function

 -

PERCENTILE worksheet function

The Kth percentile of values in an array of numbers.

Lets assume that we have 13 numbers and we want to find the value of the 45th percentile.

Calculating the Percentile - Excel

There are several ways of calculating a percentile but first lets describe the method used in Excel.

This formula will always provide you with the lowest possible percentile

Step 1 - Sort the list of numbers into ascending order and score these numbers from 1 to 13.

Step 2 - Use the following formula to calculate the corresponding ranking (split into an integer and a decimal).

Step 3 - Use the following formula to Interpolate between the necessary two numbers.

This can then be easily checked by using the worksheet function.

Calculating the Percentile - Weibull

Another common way of calculating a percentile is to use a slightly different formula.

This formula will always provide you with the highest possible percentile.

The process is exactly the same as described above except that the formula to calculate the ranking (split into an integer and a decimal) is different: