Excel PERCENTILE function

Function              PERCENTILE

Description        Excel PERCENTILE function returns the most frequently occurring value of a number set.

Syntax  PERCENTILE(array, k)

array  The array which contains data values.

k  Number, representing kth percentile.

Example:

Here I calculated 0.6 or 60% percentile in the data set shown above.

It returned an intermediate value 58.2 because the exact 60% percentile value was not present in data set.

Usage Notes:

  • Percentile calculates the kth percentile from a set of data. Percentile 40% means 40% values are lower than the value, likewise 90% percentile means all 90% values lower than the value.
  • k can be provided as a decimal (.4) or a percentage (40%)
  • k must be between 0 to 1 otherwise it provides #NUM! error.
  • If array is empty it returns #NUM! error.
  • If the k is non-numeric it supplies #VALUE! error
  • When percentile falls between values it calculates and return intermediate value