PERCENTILE Excel Function

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

PERCENTILE Function in Excel

The PERCENTILE function is responsible for returning the nth percentile from a supplied set of values. So, for example, you can use the PERCENTILE to find the 90th percentile, 80th percentile, etc.

The PERCENTILE function in Excel is a built-in function of Microsoft Excel and is categorized under the statistical function. Therefore, you can use this function as a Worksheet function (WS) in Microsoft Excel. As a Worksheet function, the PERCENTILE function can be entered as a part of any other formula in the worksheet cell.

In corporate finance, the PERCENTILE is used for analyzing the total number of employees who scored above a certain percentile in a company test. Please note that in the 2010 version of Microsoft Excel, the PERCENTILE function was replaced by PERCENTILE.INC function. In the latest versions of Excel, the PERCENTILE is still available as compatibility.

Table of contents

PERCENTILE Formula in Excel

Below is the PERCENTILE formula in Excel.

PERCENTILE formula

Parameters of PERCENTILE Function

The PERCENTILE formula accepts the following parameters and arguments:

  • array – This is the range or array from which you want the function to return the nth percentile. It is required.
  • nth_percentile – This is a number between 0 and 1, which specifies the percentile value.

Return Value

The return value of the PERCENTILE function in Excel is numeric. Please note that if the nth_percentile is not a numeric value, you may get a #VALUE! Error. Also, if the nth_percentile value is not between 0 and 1, and is greater than 1 or less than 0, then the PERCENTILE function may return #NUM! Error.

Usage Notes

  • The PERCENTILE function is responsible for calculating the “nth percentile” for a supplied set of data.
  • A percentile calculated with 0.4 as n means 40% of the values are either less than or equal to the result. Similarly, a percentile calculated with 0.9 means 90%.
  • To use the PERCENTILE function without any error, we should provide a range of values and a number between 0 and 1 for the “n” argument. For example, =PERCENTILE (range, .5) will be 50th
  • We can also specify n as a percent directly by using the % character in the formula. So, for example, =PERCENTILE (range, 80%) will be 80 percentile.
  • The “n” can be provided as either decimal or percentage.
  • We must also know that when the percentiles fall between values, the function may interpolate, and the return value will be an intermediate value.

How to Open the PERCENTILE Function in Excel?

The following are the steps used to open the percentile function in Excel:

  1. First, you can insert the desired PERCENTILE function formula in the required cell to attain a return value on the argument.

  2. You can manually open the PERCENTILE formula dialog box in the spreadsheet and insert the logical values to attain a return value.

  3. Consider the screenshot below for the PERCENTILE option under the "Statistical" function Excel menu.


    PERCENTILE Function

  4. Click on the PERCENTILE.INC option. The PERCENTILE formula dialog box may open where you can insert the argument values to obtain a return value.


    PERCENTILE Function 1

How to Use PERCENTILE Function in Excel?

Let us look below at some of the examples of the PERCENTILE function. These examples will help you explore using the PERCENTILE function in Excel.

PERCENTILE Example

Based on the above Excel spreadsheet, let us consider these examples and see the PERCENTILE function return based on the function's syntax.

PERCENTILE - Table

Consider the screenshots of the above examples for a clear understanding.

Example #1

Now apply the PERCENTILE formula in Excel here =(PERCENTILE(A2:A6,0.5))

PERCENTILE Example 1

we will get 7.6

Example 1-1

Example #2

Apply the PERCENTILE formula here: =PERCENTILE(A2:A6, 0.8)

PERCENTILE Example 2

The output is 54.4

Example 2-1

Example #3

Apply the PERCENTILE formula in Excel here: =PERCENTILE({1,2,3,4},0.8)

Example 3

Then, we will get 3.4

Example 3-1

Example #4

Now, apply the PERCENTILE formula here: =PERCENTILE({1,2,3,4},0.75)

Example 4

The output is 3.25

Example 4-1

Example #5

We have to apply the PERCENTILE formula: =PERCENTILE({7,8,9,20},0.35)

Example 5

And, we will get 8.05

Example 5-1

PERCENTILE Function Errors

If you get any error from the PERCENTILE function, then it can be any one of the following:

#NUM! – This error occurs when the supplied value of n is less than the numerical value 0 or greater than 1. Apart from this, this error can also occur if the supplied array is empty.

#VALUE! – This error occurs when the supplied n is a non-numeric value.

Things to Remember

  • The PERCENTILE function returns the nth percentile from a supplied set of values.
  • It is categorized under the statistical function.
  • The PERCENTILE.INC  function has been replaced by the PERCENTILE function. In the latest versions of Excel, the PERCENTILE function is still available as compatibility.
  • If the nth_percentile is not a numeric value, then you will get a #VALUE! Error.
  • If the nth_percentile value is not between 0 and 1 and is greater than 1 or less than 0, then the PERCENTILE function will be returning #NUM! Error.
  • The value of n should be between 0 and 1.
  • A percentile, calculated with 0.4 as n, means 40% of the values.
  • The "n" can be provided as either decimal or percentage. For example, 0.8 = 80%, 0.9 = 90% and so on.
  • If "n" is not a multiple of 1/ (n – 1), the PERCENTILE function may interpolate to determine the value at the nth percentile.