• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

Percentile Formula in Excel

Home » Excel » Statistical Functions in Excel » Percentile Formula in Excel

By Sunita Sethi Leave a Comment

PERCENTILE Formula in Excel

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:

percentile example

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:

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification 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 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

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.

percentile 1

#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.

percentile 2

  • Select the function ‘’PERCENTILE’ and then click ‘OK’.

percentile

  • On clicking ‘OK’, another dialog box appears that asks for arguments to be entered in the function. Enter the arguments and then click ‘OK’.

percentile example 1-3

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)

percentile example 1-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.

percentile example 1-5

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%)

percentile example 1-6

The result is shown below:

percentile example 1-7

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%)

 percentile example 1-8

The result is shown below:

percentile example 1-9

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)

percentile formula example 1-10

The result is shown below.

percentile formula example 1-11

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)

percentile formula example 1-12

Now the result is as follows.

percentile formula example 1-13

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)

percentile example 1-14

Now the result is as follows:

percentile formula example 1-15

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)

percentile formula example 1-16

Now the result is as follows:

percentile formula example 1-17

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
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Statistical Functions in Excel

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download PERCENTILE Formula Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Limited Period Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More