PERCENTILE.INC function returns the k’th percentile for a supplied range of values and a supplied k (between 0 and 1 inclusive)
It has been introduced in Excel 2010 and later versions to replace Excel PERCENTILE function that is still available for compatibility with Excel 2007 and earlier.
- array – array of data values
- k – value of required percentile (between 0 and 1 inclusive)
Below is some of the example of using this formula
Figure 1 – PERCENTILE.INC Formula Figure 2 – PERCENTILE.INC Result
The formula in B3
It is fine if the value of k is a decimal. It would be the same at 17%. The result is 1.02 means that there are 17% values of the array less than 1.02
Formula in B4
It returns 3 because of value 3 ranks in the middle of the array. There is 50% of values less than 3.
PERCENTILE.INC vs. PERCENTILE.EXC
PERCENTILE.INC accepts a full range of k values from 0 to 1. While PERCENTILE.EXC just accepts a range of k values from N/(N+1) and to 1/(N+1)
Figure 3 – PERCENTILE.INC vs. PERCENTILE.EXC