Percentile Formula in Excel | Guide to Use Percentile Rank Formula

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)

Formula
How to Use?

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 50^th, 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:

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

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 k^th 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

You can download this PERCENTILE Formula Excel Template here – PERCENTILE Formula Excel Template

#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 40^th 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 40^th 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)