**PERCENTILE Function (Table of Contents)**

## PERCENTILE in Excel

For **Percentile** calculation, we have a function in excel with the same name. Percentile function is used for calculating the nth percentile of any set of values below, which given the 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**

The exam reflects how well you have done in the exam as an individual; the 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 the other students’ scores 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 (8,819 ratings)

View Course

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

- From excel 2010 onwards, the 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 the Value of the 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 would 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 of 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 the 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 the PERCENTILE function in cell “E8”. Select the cell “E8”. where the PERCENTILE function needs to be applied.

Click the insert function button (fx) under the formula toolbar, the dialog box will appear, type the keyword “PERCENTILE” in the search for a function box, the 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 that I will use “0.95” as the 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 a 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**

The below-mentioned table below contains the student name in 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 the 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 the 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 a result in the cell J8. 90^{th} percentile falls between 95 & 96. Excel has interpolated between 95 to 96 score to produce the result of 95.6

### Things to Remember

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

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

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

If nth_percentile or k value is less than 0 or greater than 1, then the 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 the Excel PERCENTILE function. Here we discuss the PERCENTILE Formula and how to use the PERCENTILE function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –