For a range of values, the Excel PERCENTILE.EXC function returns the kth percentile, where k falls between the range of 0 and 1.
What is a percentile?
A percentile refers to a value that can be found below a particular percentage. The PERCENTILE.EXC function can, therefore, be used to calculate the different percentiles in a data set…from the 100th percentile to the 80th percentile.
Excel PERCENTILE.EXC Function Syntax
The syntax contains the arguments as follows:
- Array – refers to the complete data set.
- K – refers to the value of a percentile within a range.
It should be noted that both arguments in the PERCENTILE.EXC function is required elements for returns in Excel.
How to use the Excel PERCENTILE.EXC Function
As mentioned earlier, this Excel function returns the kth percentile in a data set. Here’s how to use the PERCENTILE.EXC function in Excel.
Step 1: Assuming we have a list of students with the following scores in an exam as shown in the image below.
Figure 1: Excel list of students and exam scores.
Step 2: Then we provide the values within the array, in this case, K. Besides this list is also the different percentile categories.
Figure 2: The range of values for K is listed, between 0 and 1.
Step 3: The PERCENTILE.EXC Function formula is used to get the “results”. In this case, for the first column, we have
where C3:C13, is the range of scores.
Figure 3: PERCENTILE.EXC function returns the result using the formula.
Step 4: Use the same formula for the rest of the boxes and you get the percentile for each category, thus.
Figure 4. PERCENTILE.EXC function returns the result using the formula (continued)
Common Errors with PERCENTILE.EXC Function
- The NUM! error occurs in two possible scenarios, including when the value provided for k is <1/(n+1) or >N/(n+1). N, here, refers to the number of values in the array given. This error also occurs when the array provided is void of values.
- If the value of K provided is not a number, the #VALUE! Error is returned.