Please note javascript is required for full website functionality.

Blog

A to Z of Excel Functions: the PERCENTILE Function

17 July 2023

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the PERCENTILE function.

 

The PERCENTILE function

The nth percentile of a dataset is the value that cuts off the first n% of the data values when all of the values are sorted from least to greatest.  For example, the 90th percentile of a dataset is the value that segregates the bottom 90% of the data values from the top 10% of data values.

There are three distinct functions you can use to calculate percentiles in Excel:

  1. PERCENTILE.EXC: this function returns the kth percentile of a dataset, excluding the values zero [0] and one [1]
  2. PERCENTILE.INC: this function returns the kth percentile of a dataset, including the values zero [0] and one [1]
  3. PERCENTILE: this function returns the kth percentile of a dataset as well.  It will return the same value as the PERCENTILE.INC function.

In almost all cases, it is probably best to use the PERCENTILE.INC function because this function includes the values zero [0] and one [1] when calculating the percentiles and is the function that effectively “replaces” PERCENTILE.

No matter which of the three functions you use to calculate percentiles, the difference between the values calculated by PERCENTILE.EXC and PERCENTILE.INC will be similar in most cases.  In some instances, it’s even possible that the two functions will return precisely the same values depending upon the numbers analysed.

The PERCENTILE function is a statistical function that calculates the value at a given percentile in a dataset.  It considers the range between zero [0] and one[1] including the extreme values.  This function is particularly useful when analysing data distributions and understanding the relative position of a specific value within a dataset.  For example, you can decide to examine candidates who score above the 90th percentile.

The PERCENTILE function has the following syntax:

=PERCENTILE(array, k)

It contains two arguments:

  • array which is required and represents the array of values or range of data that defines relative standing
  • k which is also required is a value 0 ≤ k ≤ 1 (0..1 inclusive), which represents the percentile (or kth percentage).

It should be noted that:

  • this function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage.  Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel
  • if array is empty, PERCENTILE returns the #NUM! error value
  • if k is nonnumeric, PERCENTILE returns the #VALUE! error value
  • if k is < 0 or if k > 1, PERCENTILE returns the #NUM! error value
  • if k is not a multiple of 1/(n + 1) (where n is the number of values in the array), PERCENTILE interpolates to determine the value at the kth percentile
  • PERCENTILE will interpolate when the value for the specified percentile lies between two [2] values in the array.  If it cannot interpolate for the percentile, k specified, Excel will return #NUM!error
  • the array argument ignores text, logic, date and time values.

Please see my examples below: 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here. 

Newsletter