Updated May 3, 2023
PERCENTILE in Excel
For Percentile calculation, we have a function in Excel with the same name. The 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 the percentile at the 5th value, then we will get the percentile below the 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 based on 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 also depends on the other students’ scores.
Percentile = Number of students who scored less than you/Total number of students x 100
If your score or mark is 60th out of 100 students, your score is better than 60 people’s; hence, your percentile is 60%ile. Here it indicates what percent of students you are ahead of, including yourself.
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 reports scores in exams or tests, like GRE, GMAT, and other entrance exams.
- PERCENTILE.INC is an updated version that 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 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 95th Percentile In Entrance Exam
The table below contains the student’s name in 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, and the PERCENTILE function will appear in the Select a function box. Three options appear in the select a function box, i.e. PERCENTILE, PERCENTILE.EXC and PERCENTILE.INC function. The PERCENTILE function was previously used for Excel 2007 & earlier versions. 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 the Array argument, click inside cell C8 to see the cell selected, then Select the cells until 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 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. 95th percentile falls between 93 & 96. Excel has interpolated between 93 to 96 scores to produce the result 93.6.
PERCENTILE in Excel – Example #2
PERCENTILE Function To Calculate 90th Percentile In Entrance Exam
The below-mentioned table below contains the student’s names in column G (G6 to G20) & their GRE 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 the Array argument, click inside cell H6 to 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 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. 90th percentile falls between 95 & 96. Excel has interpolated between 95 to 96 score to produce the result of 95.6
Things to Remember
In the percentile function, one can also input an individual number and an array argument using the below format.
=PERCENTILE({6,8,10,12}, 0.35)
If the nth_percentile or k value is not numeric (Non-numeric), then the PERCENTILE function will return the #VALUE! Error.
If the 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 –
