PERCENTILE Function in Excel – Get the percentile value in Excel

PERCENTILE Function in Excel returns the value of K^thpercentile for a supplied range of values and supplied k.

Syntax of PERCENTILE Function in Excel:

PERCENTILE( array, k)

array – The array of data values for which you want to calculate the K^th percentile.
K – value of required percentile (either between 0 and 1 or between 0% and 100%)

Example of PERCENTILE Function in Excel:

Let’s look at an example on how to calculate percentile using PERCENTILE Function in Excel

Formula:

First Example Calculates the 5^th percentile by interpolating the values which result in 1(by taking range and percentage value for k as input).
Second and third examples calculate 20^th and 70^th percentile respectively by taking the range and decimal value of k as input.
last Example Calculates the 99^th percentile by interpolating the values which result in 19.8 (by taking range and percentage value for k as input).

So the result will be

Result:

Note: #NUM! Error Occurs if

The supplied value of k is less than 0 or greater than 1
The supplied array is empty.