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.

PERCENTILE Formula in Excel

Below is the PERCENTILE formula in Excel.

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 50thWe 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?

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.

  • First, you can insert the desired PERCENTILE function formula in the required cell to attain a return value on the argument. You can manually open the PERCENTILE formula dialog box in the spreadsheet and insert the logical values to attain a return value. Consider the screenshot below for the PERCENTILE option under the “Statistical” function Excel menu. 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.

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

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))

we will get 7.6

Example #2

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

The output is 54.4

Example #3

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

Then, we will get 3.4

Example #4

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

The output is 3.25

Example #5

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

And, we will get 8.05

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.

This article is a guide to the PERCENTILE in Excel. We discuss the PERCENTILE formula in Excel and how to use the PERCENTILE function in Excel, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • Margin of ErrorMargin Of ErrorThe margin of error is a statistical expression to determine the percentage point the result arrived at will differ from the actual value. Standard deviation divided by the sample size, multiplying the resultant figure with the critical factor. Margin of Error = Z * ơ / √nread morePercent Error FormulaPercent Error FormulaPercentage error formula is calculated as the difference between the estimated number and the actual number in comparison to the actual number and is expressed as a percentage, to put it in other words, it is simply the difference between what is the real number and the assumed number in a percentage format.read moreVBA Worksheet FunctionVBA Worksheet FunctionThe worksheet function in VBA is used when we need to refer to a specific worksheet. When we create a module, the code runs in the currently active sheet of the workbook, but we can use the worksheet function to run the code in a particular worksheet.read moreFormula Errors in ExcelFormula Errors In ExcelWhen we insert a formula and forget to include some required input, we get an error in Excel. Some of the most common errors in Excel formulas are #NAME?, #DIV/0!, #REF!, #NULL!, #N/A, ######, #VALUE! and #NUM!.read moreRedo Shortcut in ExcelRedo Shortcut In ExcelIn Excel, we have an option named “Undo” that we may use by pressing Ctrl + Z. When we undo an action but subsequently realize it was not a mistake, we can cancel the undo action and return to the original point, using “Redo.“read more