PERCENTILE Function in Excel returns the value of Kth percentile 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 Kth 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 5th percentile by interpolating the values which result in 1(by taking range and percentage value for k as input).
- Second and third examples calculate 20th and 70th percentile respectively by taking the range and decimal value of k as input.
- last Example Calculates the 99th 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.