Percentile Function

In Microsoft Excel, the percentile can be defined as a value of a variable of interest at or below which a defined percent of values of that variable occur. In more logical terms, it allows the user to calculate the k-th percentile. This powerful function determines outliers, analyzes employee performance, evaluates post-graduate and graduate student's academic performance, and assesses health and risk metrics.

For example, if a series rating falls below 95%, the series is ranked among the top 95% series list. In Excel, the percentile function can calculate the percentile data.

Compatibility

This advanced excel function is compatible with the following versions of MS-Excel −

Excel for Microsoft 365
Excel for Microsoft 365 for Mac
Excel for the web
Excel 2021
Excel 2021 for Mac
Excel 2019
Excel 2019 for Mac
Excel 2016

The PERCENTILE function interchanges the PERCENTILE.INC function from Excel 2010.

Syntax

The syntax of Percentile function is as follows −

PERCENTILE.INC(array, k)

Arguments

You can use the following arguments with the Percentile function −

Argument	Description	Required / Optional
Array	It specifies the array or data range for which the percentile is calculated.	Required
K	It denotes the percentile value in the range from 0 to 1, inclusive.	Required

Points to Remember

The PERCENTILE() function, will calculate the value at a given percentile of a dataset.
The PERCENTILE() function will retrieve the error named #NUM! if the array is empty.
If the k value is non-numeric in a PERCENTILE() function, then the function will return the #VALUE! Error.
If the value of k is less than 0 or greater than 1, then the PERCENTILE() function will return the #NUM! Error.

Examples of Percentile Function

Practice the following examples to learn the use of the Percentile function in Excel.

Example 1

Step 1 − In this example, we will consider a score values column that contains array values from the D3:D8 data cell, and the k-value is present in the F5 cell.

Step 2 − Now, you can enter the formula =Percentile(D3:D5,F5) to calculate the required stats from the provided data in the D10 cell.

Step 3 − After that, press the Enter key. The resultant obtained value is 4.5.

Step 4 − Therefore, the 70th percentile of the score dataset is approximately 4.5. This means that 70% of the scores in the dataset are equal to or below 4.5.

Example 2

The PERCENTILE() function, will return the error value, #NUM! if the array is empty.

Solution

Step 1 − In this example, we will consider an empty array with a k value of 0.6.

Step 2 − In the next step, enter the PERCENTILE() function, =PERCENTILE(C3:C6, E3) in the C8 cell by passing the array size C3 to C6 and k value as an E3 cell reference.

Step 3 − After that, press the Enter key and the result will display a #NUM! value, as provided below:

Example 3

If the value of k is non-numeric in a PERCENTILE() function, then the function will return the #VALUE! Error.

Solution

Step 1 − First, assume the sample dataset where the array values are specified in the cell range C3:C6 and the k value as a non-numeric defined in the E3 cell.

Step 2 − Applying the percentile() function =PERCENTILE(C3:C6,E3) in the C8 cell by passing the array and k-value cell references.

Step 3 − After that, press the Enter key. This will display the #VALUE! error.

Example 4

If the value of k is less than 0, or greater than 1, then the PERCENTILE() function will return the #NUM! Error.

Solution

For k greater than 1

Step 1 − For k greater than 1, you can assume that the value of k is 1.4, as specified in the E3 cell.

Step 2 − After that, enter the formula =PERCENTILE(C3:C6,E3) in the C8 cell , bypassing the array values and the k-value.

Step 3 − Finally, the resultant value will generate the #NUM! Error, as the k value is greater than 1.

For k less than 0

In this case, we will assume that the k-value is -1.2 as shown in the E3 cell.

Step 4 − After that, apply the percentile () function =PERCENTILE(C3:C6,E3) in the C8 cell. Since here the k-value is less than 0, that is negative. This function will generate a #NUM! error.

Save or share this article