PERCENTILE.EXC Function in Excel

PERCENTILE.EXC Function in Excel returns the value of Kth percentile for a supplied range of values and supplied k. Where the value of k is between 0 and 1 and both the values are excluded

 Syntax of PERCENTILE.EXC Function in Excel:

PERCENTILE.EXC( 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%) with both the values are excluded

 

Example of PERCENTILE.EXC Function in Excel:

Formula

PERCENTILE.EXC FUNCTION IN EXCEL 1

Note: BOTH Minimum and Maximum Values (0 and 20) are excluded for calculating PERCENTILE.EXC

  • PERCENTILE.EXC Function can be able to interpolates the values only when the k > 1/(n+1) and K< n/(n+1) where n is the number of elements in the array.
  • In our example n=11 and PERCENTILE.EXC Function can be able to interpolates the values when k > 0.0833(8.3 %) and k < 0.9167 (91.67%), so first and last example results in #NUM! error.
  • 20th,70th and 91.66th percentile are calculated with PERCENTILE.EXC Function.

So the result will be

Result:

PERCENTILE.EXC FUNCTION IN EXCEL 2

Note: #NUM! – Error occurs if

  • The supplied value of k is < 1/(n+1) or > n/(n+1) (where n is the number of values in the supplied array)
  • The supplied array is empty.

previous-small PERCENTILE.EXC Function in Excel                                                                                                                next_small PERCENTILE.EXC Function in Excel