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
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:
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.
