Percentile is used in calculations for various analysis and it is known as the K th percentile of a given data set, in excel 2007 and older versions we had an inbuilt Percentile formula which is used to calculate the same for us, in the newer versions we have different percentile formula as Percentile.EXC and Percentile.INC.
Percentile Formula in Excel (Table of Contents)
Excel Percentile Formula
The Syntax for PERCENTILE formula in excel is as follows:
The PERCENTILE formula in excel has the following arguments:
- Array: Represents the array of data values for which k’th percentile is to be calculated
- Argument2: Represents the value of required percentile. It should be a decimal number between 0 & 1. ‘k’ can also be specified as a percent using the ‘%’ character.
The k-th percentile is that number such that k% of all data values are less and (100-k)% are more than it.
The PERCENTILE formula in Excel calculates the ‘kth percentile’ for a given range of values and a given value of ‘k’. The function returns a value greater than or equal to specified percentile.
What is the Percentile Rank Formula in Excel?
A percentile is that number below which a given percentage of values in a list of values fall. It is a measure of an individual’s performance relative to others, i.e how one specific data value compares to the rest of the data. For instance, if a student’s score or marks is 50th, out of 100 students, then that means that the student’s score is better than other 50 students and so the percentile is 50%. It indicates that what percent of students that student is ahead of.
The general formula to find the percentile that corresponds to a given value x is:
Percentile value of x= ((No. of values less than x)/ (Total no. of values))*100
This function applies to Excel 2013, Excel 2016, Excel 2019, Excel 2007, Excel 2003, Excel 2000, Excel for Office 365, Excel 2011 for Mac, Excel XP.
We can think of using PERCENTILE function in many potential cases like:
4.9 (1,353 ratings)
- Creating or building a source of acceptance, or to set a threshold of acceptability or rejection
- If we wish to take a decision where we need to examine people who have scored above kth percentile
- Report standardized scores in tests or exams
- Calculating ranks among other students’ test scores
- Companies’ price earnings ratio, etc….
Two Different Ways to Insert PERCENTILE Formula in Excel
#1 – Click on the cell where percentile is to be computed, then type ‘=’ followed by ‘PERCENTILE, and enter the arguments for which percentile is desired.
#2 – Under the ‘Formulas’ bar, there is an icon of ‘Insert Function’. On clicking this, a dialog box appears that shows a list of excel functions.
- Select the function ‘’PERCENTILE’ and then click ‘OK’.
- On clicking ‘OK’, another dialog box appears that asks for arguments to be entered in the function. Enter the arguments and then click ‘OK’.
How to Use Percentile Formula in Excel? (with Examples)
Following are the ways with which excel PERCENTILE formula can be used.
Example #1
If we wish to calculate 40th percentile of a list of values that are stored in cells A2: A6, then the PERCENTILE function would work as follows:
=PERCENTILE(A2:A6,0.4)
We can see in the above screenshot that the formula returns 40th percentile (4.8) of numerical cells in the range: A2: A6. This implies that 40% of the values (i.e 2 out of 5) in A2: A6 are lower than or equal to 4.8.
Example #2
Now, let’s say in the below example we enter the value of ‘k’ as a percentage instead of decimal, then the Excel PERCENTILE function would work as follows:
=PERCENTILE(A2:A6,40%)
The result is shown below:
We can see in the above screenshot that the formula returns the same result as in Example 1, with ‘k’ entered as a percentage.
Example #3
Now, let’s say in Example 1 we enter the range or array directly as arguments, then PERCENTILE function in excel would work as follows:
=PERCENTILE(2,3,8,7,6,40%)
The result is shown below:
Example #4
If the value of ‘k’ entered is less than 0, then the excel PERCENTILE function would work as follows:
=PERCENTILE(A2:A6,-0.2)
The result is shown below.
We can see in the above screenshot that when ‘k’ is less than 0 (-0.2), then the formula above returns #NUM! Error value.
Example #5
If the value of ‘k’ entered is greater than 1, then the Excel PERCENTILE function would work as follows:
=PERCENTILE(A2:A6,1.2)
Now the result is as follows.
We can see in the above screenshot that when ‘k’ is greater than 1 (1.2), then the formula above returns #NUM! Error value.
Example #6
If the value of ‘k’ entered is non-numeric, then the PERCENTILE function in Excel would work as follows:
=PERCENTILE(A2:A6,B8)
Now the result is as follows:
We can see in the above screenshot that when ‘k’ is non-numeric (abc), then the formula above returns #VALUE! Error value.
Example #7
Now if the supplied array entered as an argument to the function is empty, then the excel PERCENTILE function would work as follows:
=PERCENTILE(A2:A6,0.6)
Now the result is as follows:
We can see in the above screenshot that the supplied array i.e A2: A6, entered as an argument to the function is empty/blank, so in this case, the formula returns #NUM! Error.
Things to Remember
- PERCENTILE function is a built-in function in Excel and is categorized as a statistical excel function.
- PERCENTILE will interpolate when the value for specified percentile is between two values amongst the array values.
- Value of ‘k’ can be entered as a percentage or a decimal.
- PERCENTILE returns a numeric value.
- Excel PERCENTILE function returns the value of what a given percentile rank would be and not the percentile rank.
- If ‘k’ is non-numeric, then PERCENTILE function returns #VALUE! Error.
- If ‘k’ is less than 0 or greater than 1, then excel PERCENTILE function returns #NUM! Error.
- If supplied array entered as an argument to the function is empty, then PERCENTILE formula returns #NUM! Error.
- In the latest versions of Excel, when we enter or type PERCENTILE function in a cell, then two variants of PERCENTILE function appear PERCENTILE.EXC and PERCENTILE.INC. These two functions return kth percentile of values in the range 0-1, exclusive and inclusive respectively. The PERCENTILE function is same as PERCENTILE.INC function
- EXC function will interpolate when k is not a multiple of 1/(n+1), where n is the size of the input array entered as an argument to the function
- PERCENTILE function can be used as a worksheet function, and when entered as a worksheet function, it can be entered as a part of any other formula in the worksheet cell
- In the latest versions of Excel, PERCENTILE.INC function can also be used as a VBA function.
Recommended Articles
This has been a guide to PERCENTILE Formula in Excel. Here we discuss how to use the Percentile Rank Formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
- Calculate the Percent Change in Excel
- Insert Function in Excel
- Percentile Rank Formula
- VBA DIR
- VBA IsEmpty
- List of Advanced Excel Formulas
- Basic Excel Formula List
- Calculate Percent Error in excel
- Excel Formula Not Working
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
