The PERCENTILE.EXC function in Excel which calculates the kth percentile. It tells us the percentage of values a certain percent of the whole values fall under. Here, K represents the number 0 to 1. In this tutorial, we will learn how to use the PERCENTILE.EXC function in Excel.
Figure 1. Example of How to use the PERCENTILE.EXC Function
=PERCENTILE.EXC (array, k)
The PERCENTILE.EXC uses two arguments. They are:
- Array – This argument is required. It represents the set of values from which we want to extract the kth percentile.
- K – This argument is also required. Any value between 0 to 1. It represents the Kth percentile.
The PERCENTILE.EXC function in Excel works on a set of values to find out the kth percentile. This represents the value below which k percent of values fall under. This function is very useful for financial statements. We need to keep in mind that the value of k has to be within 0 to 1.
Setting up Data
The following example uses an employee information data set. Column A, B and C contains the names, overtime and k values.
Figure 2. The Data Set to be Used
To find the results in column D using the PERCENTILE.EXC formula, we need to:
- Select the cell D2.
- Insert the formula
- Press Enter.
Figure 3. Applying the Formula to the Data Set
- Drag the formula from cells D2 to D6 to copy the formula to the entire column.
- Excel returns a #NUM error if the value of K is bigger than n/(n+1) or smaller than 1/(n+1).
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.