Home > Microsoft Excel > How to Calculate Percentile in Excel? 3 Useful Formulas
(Note: This guide on how to calculate percentile in Excel is suitable for all Excel versions includingOffice 365)
Excel is well-known for its ability to store a wide variety of data. It is the most preferred software application to add, analyze, and retrieve data.
Keeping track of details and particulars from an individual to an organizational level is effortless with the help of Excel.
Additionally, there might be a lot of functions and operations such that one result provides the operands to perform another operation. This involves addition, subtraction, total, and average associated with the data.
One such operation is called Percentile.
In this article, I will tell you what percentile is and how to calculate percentile in Excel using 3 easy ways.
You’ll Learn:
- What Is a Percentile?
- What Is the PERCENTILE Function in Excel?
- Syntax
- How to Calculate Percentile in Excel?
- Usage Criteria
Related Reads:
How to Use SUMPRODUCT Function in Excel? 5 Easy Examples
How to Use the PROPER Function in Excel? 3 Easy Examples
Excel DATEVALUE– A Step-by-Step Guide
What Is a Percentile?
A percentile is a unit of measurement in statistics. It is defined as the total percentage of scores that fall under a particular value. In layman’s terms, it is a measure of how a value performs in comparison with the other values in the same set. Using the percentile value, you can ascertain the relative position of the value in the dataset.
Most scoring systems, like the competitive exams, use percentile as a value to measure the rank and determine the pass/fail criteria.
Consider an example where 60 students appear for an exam and the exam is conducted for 300 marks. In this case, the person who scored the highest marks sets the threshold for the next candidates. If you say one certain student has scored 50th percentile, then this means that the student has scored above 50% of the rest of the students. If you say one person has scored 60th percentile, this means that the person has scored more than 60% of students.
The 25th percentile is known as the first quartile (Q1), the 50th percentile as the second quartile (Q2), and the 75th percentile as the third quartile (Q3).
What Is the PERCENTILE Function in Excel?
In Excel also, you can calculate percentiles from a set of values easily using the PERCENTILE function. There are three variations of the PERCENTILE function in Excel.
- PERCENTILE
This function is in operation from the older versions and is still in place for backward compatibility purposes.
- PERCENTILE.INC
This is a newer version of the PERCENTILE function which has the same functionality as the PERCENTILE function. This is the most used function to calculate the percentile.
- PERCENTILE.EXC
This is a function introduced for newer versions of Excel. The value of k is between the range 0 to 1, but exclusive of 0 and 1.
Also Read:
How to Fix the #Div/0 Error in Excel? 2 Easy Methods
How to Use ISTEXT in Excel? With 3 Different Methods
IFERROR Excel-The Ultimate Guide to Catching Errors in Excel
Syntax
The syntax for the percentile function is:
=PERCENTILE(cellrange,k)
Where cellrange is the range of cells that house the values of the dataset and
k is the percentile value. For example, if you want to calculate the 50th percentile, enter 0.5 in the place of k.
The syntax is also similar to the other two versions:
- =PERCENTILE.INC(cellrange,k)
- =PERCENTILE.EXC(cellrange,k)
How to Calculate Percentile in Excel?
The PERCENTILE function returns the specified k-th percentile from an array. Using the PERCENTILE function is just like using any other function.
I will show you how to calculate percentile in Excel with the help of an example. Consider a list of students appearing for an exam. The exam is conducted for a total of 300 marks. We have consolidated 10 students from class-A and the respective marks they have scored.
Enter the set of values in a column and their respective marks into another column.
To calculate the percentile, select a destination cell and enter the formula =PERCENTILE. As you start typing, Excel shows you suggestions related to the PERCENTILE function along with its description.
To choose the function, you can use the up or down arrows to move and use the Tab key to select the function. Or, you can use the mouse and double-click on the selected function.
In this case, let us select the PERCENTILE.EXC function. Once the function is selected, we can pass the arguments.
For the array parameter, you can either enter the range of cells that houses the values or you can drag and select the range of cells and they will automatically populate the array parameter in the function.
Add a comma, and then enter the percentile value you want to calculate. For example, if you want to calculate the 90th percentile for the given set of data, enter 0.9 in the place of k. If you want to calculate the 50th percentile for the given set of data, enter 0.5 in the place of k.
Note: You can also specify the k value in terms of percentage like 0.8 as 80% or 0.9 as 90%
Press Enter.
This gives you the percentile value based on the list of entries. According to our example, this gives you the value of the 50th percentile based on the given array of numbers. That is out of the 10 entries ranging from 295 to 123, the 90th percentile value is 294.4.
In real-time, this means that keeping the 90th percentile as the threshold, the number of persons who have scored above 294.4 have passed the eligibility criteria.
If you want to calculate the 90th percentile of the given set of values excluding the k values 0 and 1, you can use the =PERCENTILE.INC function.
If you want to calculate the 50th percentile, you can use the above-mentioned method to arrive at the percentile value.
Usage Criteria
- You can calculate percentile only for an array or a given set of values.
- The percentile parameter (k) must be between 0 and 1, or else the PERCENTILE function will return a #NUM! error
- If the percentile parameter (k) is a non-numeric value, the PERCENTILE function will return a #VALUE! error.
Suggested Reads:
How to Add Leading Zeros in Excel? 4 Easy Methods
How to Create an Excel Slicer? 2 Easy Ways
How to Create a Step Chart in Excel?
Closing Thoughts
In this article, we saw how to calculate percentile in Excel using the =PERCENTILE formula. You can =PERCENTILE.INC or =PERCENTILE.EXC to calculate the percentile values based on your needs.
You will always have to deal with percentiles for academic or official purposes. Especially when more entries are involved and you have to work with percentiles, the =PERCENTILE function aids your needs. Once you have calculated the percentile, you can use other options to filter out and segregate the entries.
Please visit ourfree resourcescenter for more high-quality guides on Excel and other Microsoft Suite applications.
Ready to dive deep into Excel? Clickherefor advanced Excel courses with in-depth training modules.
Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to100+ IT training courses.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
