Excel Functions Tutorials

- Statistical Function in Excel
- AVERAGE Excel Function
- Average Formula in Excel
- AverageIF in Excel
- AVERAGEIFS Function in Excel
- CORREL Excel Function
- Correlation Matrix in Excel
- Correlation vs Covariance
- COUNT Excel Function
- Count Formula in Excel
- COUNTA Excel Function
- COUNTIF Excel Function
- COUNTIF Formula in Excel
- COUNTIFS Function in Excel
- COUNTIF with Multiple Criteria
- COUNTIF Examples
- FORECAST Excel Function
- Forecast Formula in Excel
- FREQUENCY Excel Function
- Frequency Formula in Excel
- GROWTH Excel Function
- Growth Formula in Excel
- LARGE Excel Function
- LINEST Excel Function
- Linear Regression in Excel
- Lognormal Distribution in Excel
- MAX Excel Function
- Max Excel Formula
- Mean vs Median
- MEDIAN Excel Function
- MEDIAN Formula in Excel
- MIN in Excel
- MODE Excel Function
- NORM.S.INV Function in Excel
- NORMDIST in Excel
- PERCENTILE Excel Function
- Percentile Formula in Excel
- Percentile Rank Formula
- Poisson Distribution in Excel
- P-Value in Excel
- QUARTILE Excel Function
- RANK Function in Excel
- SLOPE Function in Excel
- SMALL Function in Excel
- Standard Deviation in Excel
- Standard Deviation Formula in Excel
- TREND Function in Excel
- T-TEST in Excel
- Chi Square Test in Excel
- Variance vs Standard Deviation
- Weibull Distribution in Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
- Power Bi (35+)

**Percentile Function in Excel (Table of Contents)**

## PERCENTILE Function in Excel

The PERCENTILE Function is responsible for returning the nth percentile from a supplied set of values. For example, you can use the PERCENTILE to find the 90^{th} percentile, 80^{th} percentile, etc.

The PERCENTILE in excel is a built-in function of Microsoft Excel and is categorized under the Statistical Function. You can use this function as a worksheet function (WS) in Microsoft Excel, and as a worksheet function, PERCENTILE function can be entered as a part of any other formula in the worksheet cell.

In the field of corporate finance, the PERCENTILE is used for analyzing the total number of employees who have 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, and in the latest versions of Excel, the PERCENTILE is still available as compatibility.

### PERCENTILE Formula in Excel

Below is PERCENTILE Formula in Excel.

#### Parameters of PERCENTILE Function

The PERCENTILE Formula ccepts 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 a numeric value. Please note that if the nth_percentile is not a numeric value, then you will 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 will be returning #NUM! error.

### Usage Notes

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

### How to Open PERCENTILE Function in Excel?

**1)** You can simply enter 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 dialogue box in the spreadsheet and enter the logical values to attain a return value.

**3) **Consider the screenshot below to see the PERCENTILE option under the Statistical Function excel menu.

**4) **Click on the PERCENTILE.INC option. The PERCENTILE formula dialogue box will open where you can put the argument values to obtain a return value.

**How to Use PERCENTILE Function in Excel?**

Let’s look below at some of the examples of PERCENTILE function. These examples will help you in exploring the use of PERCENTILE function in Excel.

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

Consider the below screenshots of the above examples for 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)**

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

Output is **3.25**

#### Example #5

Here 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 kind of error from the PERCENTILE Function, then it can be any one of the following-

#NUM! – This kind of 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 kind of error occurs when the supplied n is a non-numeric value.

**Things to Remember**

- It is the function which is responsible for returning the nth percentile from a supplied set of values.
- It is categorized under the Statistical Function.
- This function has been replaced by PERCENTILE.INC Function, and 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 which is calculated with 0.4 as n means 40% of the values.
- 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), PERCENTILE interpolates to determine the value at the nth percentile.

### Recommended Articles

This has been a guide to PERCENTILE in Excel. Here we discuss the PERCENTILE Formula in excel and how to use PERCENTILE function Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

- IsEmpty in Excel VBA
- Margin of Error Formula | Explanation | Examples
- Percent Error Formula Calculator
- Excel VBA Worksheet
- Excel VBA FIND
- Formula Errors in Excel
- COMBIN Excel Function
- OFFSET Excel Function
- FIND Function in Excel
- Row Function in Excel