**PERCENTILE Function (Table of Contents)**

## PERCENTILE in Excel

For **Percentile** calculation we have a function in excel with same name. Percentile function is used for calculating the nth percentile of any set of values below which given percentage of observations of the selected set of values falls. Suppose, we have 10 numbers, for which we calculate percentile at 5^{th} value, then we will get the percentile below selected Kth value.

**Difference between Percentile & percentage function**

**Percentage function**

In exam, it reflects how well you have done in exam as an individual, Percentage score is calculated on the basis of the below-mentioned formula

Percentage = Marks scored/Total score x 100 **e.g.** If marks are 80, 80/100 x 100 = 80%

**Excel Percentile Function**

Percentile is a measure of your performance relative to others, it depends on scores of the other students also

Percentile = Number of students scored less than you/Total number of students x 100

Suppose, if your score or marks is 60th, out of 100 students that means your score is better than 60 people and hence your percentile is 60%ile. Here it indicates what percent of students you are ahead of, including yourself

4.8 (3,685 ratings)

View Course

When you enter PERCENTILE function in excel cell, two variants of Percentile function appear PERCENTILE.EXC and PERCENTILE.INC function. (Explained in next page)

- From excel 2010 onwards, Percentile function is replaced by PERCENTILE.EXC and PERCENTILE.INC function.
- Excel Percentile function is used to report scores in exams or tests, like GRE, GMAT and other entrance exams.
- PERCENTILE.INC is an updated version which is most commonly used to calculate percentile
- PERCENTILE.INC is used as both worksheet function & VBA function in Excel

**PERCENTILE Formula in Excel:**

Below is the Excel PERCENTILE Formula:

** **

The Excel Percentile function has the below-mentioned argument

**Array (Compulsory or required argument):** The array of data values for which you want to calculate the Kth percentile.

**K (Compulsory or required argument):** It is the percentile value you are looking for or Value of required percentile (It is either between 0% and 100% or between 0 and 1)

e.g. if you wanted to find out the value for the 60th percentile, you will use “0.6” as your percentile value. (Note: K is any percentage expressed as a decimal, such as 0.20 for 20%)

**PERCENTILE.INC (INC is inclusive):** means the value of k is within the range 0 to 1 Inclusive, When the value of K, is in the range of zero to one you will get a result.

**PERCENTILE.EXC (EXC is exclusive):** The value of k is within the range 0 to 1 exclusive. An Error occurs if you use a value of K which is outside the valid range for the data set.

### How to use PERCENTILE Function in Excel?

PERCENTILE Function is very simple to use. Let us now see how to use PERCENTILE function in Excel with the help of some examples.

#### PERCENTILE in Excel – Example #1

**PERCENTILE**** Function To Calculate 95 ^{th} Percentile In Entrance Exam**

In the below-mentioned table, it contains student name in the column B (B8 to B24) & Their score in column C (C8 to C24) I need to find out the score for the 95th percentile

Let’s apply PERCENTILE function in cell “E8”. Select the cell “E8”. where PERCENTILE function needs to be applied.

Click the insert function button (fx) under formula toolbar, the dialog box will appear, Type the keyword “PERCENTILE” in the search for a function box, PERCENTILE function will appear in select a function box. Three options appear in select a function box i.e. PERCENTILE, PERCENTILE.EXC and PERCENTILE.INC function. The PERCENTILE function was previously used for excel 2007 & earlier version. So, you need to select PERCENTILE.INC function. (The value of k is within the range 0 to 1 inclusive). Double click on PERCENTILE.INC.

A dialog box appears where arguments for PERCENTILE.INC function needs to be filled or entered i.e. =PERCENTILE (array, k)

i.e. **=PERCENTILE.INC(C8:C24,0.95)** Here the score data is present in the range (C8 to C24) for which we need to apply PERCENTILE.INC function

To enter Array argument, click inside cell C8 and you’ll see the cell selected, then Select the cells till C24. So that column range will get selected i.e. C8:C24

K, it is the percentile value we are looking for. Here I need to find out the value for the 95th percentile, so, I will use “0.95” as percentile value. (K is any percentage expressed as a decimal, i.e. 0.95 for 95%) Click ok.

After entering both the arguments. **=PERCENTILE.INC (C8:C24,0.95)** i.e. returns the score for the 95th percentile i.e. 93.6 as the result in the cell E8. 95^{th} percentile falls between 93 & 96. Excel has interpolated between 93 to 96 score, to produce the result 93.6.

#### PERCENTILE in Excel – Example #2

**PERCENTILE ****Function To Calculate 90 ^{th} Percentile In Entrance Exam**

In the below-mentioned table, it contains student name in the column G (G6 to G20) & their GRE exam score in column H (H6 to H20). I need to find out the score for the 90th percentile

Let’s apply PERCENTILE.INC function in cell “J8”. Select the cell “J8”. where PERCENTILE. INC function needs to be applied.

Click the insert function button (fx) under formula toolbar, the dialog box will appear, Type the keyword “PERCENTILE.INC” in the search for a function box, PERCENTILE.INC function will appear to select a function box. Double click on PERCENTILE.INC.

A dialog box appears where arguments for PERCENTILE.INC function needs to be filled or entered i.e. =PERCENTILE (array, k)

i.e. **=PERCENTILE.INC(H6:H20,0.90)** Here the score data is present in the range (H6 to H20) for which we need to apply PERCENTILE.INC function

To enter Array argument, click inside cell H6 and you’ll see the cell selected, then Select the cells till H20. So that column range will get selected i.e. H6:H20

K, it is the percentile value we are looking for. Here I need to find out the value for the 90th percentile. So, I will use “0.90” as percentile value. (K is any percentage expressed as a decimal, i.e. 0.90 for 90%) Click ok.

after entering both the arguments.** =PERCENTILE.INC(H6:H20,0.90)** i.e. returns the score for the 90th percentile i.e. 95.6 as the result in the cell J8. 90^{th} percentile falls between 95 & 96. Excel has interpolated between 95 to 96 score, to produce the result 95.6

### Things to Remember

In percentile function, the array argument can also be entered as an individual number in the below-mentioned format.

**=PERCENTILE({6,8,10,12}, 0.35)**

If nth_percentile or k value is not a numeric value (Non-numeric), then PERCENTILE function will return the #VALUE! error.

If nth_percentile or k value is less than 0 or greater than 1, then PERCENTILE function will return the #NUM! error.

If the supplied array argument is empty, then also #NUM! error occurs

### Recommended Articles

This has been a guide to Excel PERCENTILE function. Here we discuss the PERCENTILE Formula and how to use PERCENTILE function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –