It gives you an idea of where a value lies in the dataset (i.e., its position/rank in the dataset). In practical life, I have seen the percentile value being used in competitive exams, where on the given score, you get the percentile value. This tells you where you stand in comparison to all the other people who appeared for that exam. In this tutorial, I will explain everything you need to know about the percentile function in Excel, and show you examples of how to calculate the 90th percentile or 50th percentile in Excel. So let’s get started!
What is Percentile? An Easy Explanation!
Percentile value tells you the relative position of a data point in the whole dataset. For example, if I have the scores of 100 students and I tell you that the 90th percentile score is 84, it means that if anyone scores 84, then their score would be above 90% of the students. Similarly, if the 50th percentile value for a dataset is 60, it means that anyone who got a score of 60 has about 50% of the people with better scores and about 50% of the people with a lesser score. This is a preferred method as it’s more meaningful than just giving the score. For example, if I tell you that your score is 90, it doesn’t tell you where you stand relative to the others. But if I tell you that your score’s percentile is 90th, you immediately know that you have done better than 90% of the people who took the exam. Calculating the percentile value in Excel is very easy as it has some inbuilt functions to do this.
PERCENTILE Functions in Excel
There are three variations of the percentile function available in Excel. If you’re using Excel 2010 or versions after that, you will have access to all these three functions.
PERCENTILE – this is old function that is now kept for backward compatibility purposes. You can use this, but it’s best to use the new ones (if you have those in your version of Excel). The result of this function is a value between 0 and 1PERCENTILE.INC – this is the new formula (which works exactly like the PERCENTILE function). In most cases, this is the function you would need to use. The result of this function is a value between 0 and 1PERCENTILE.EXC – this also works like the PERCENTILE.INC function with one difference – the result of this function will be a value between 0 and 1, but excludes K values between 0 to 1/(N+1) as well as N/(N+1) to 1 (where N is the size of the sample)
To put it simply, use PERCENTILE.INC in most cases (and if you are using Excel 2007 or prior versions, use PERCENTILE function) Below is the syntax of the PERCENTILE.INC function in Excel: where: The syntax remains the same for the PERCENTILE and PERCENTILE.EXC functions.
Calculating 90th Percentile in Excel (or 50th Percentile)
Suppose you have a dataset as shown below and you want to know the 90th percentile value for this dataset. Below is the formula that will give you the 90th Percentile: In the above formula, I have used 90% as the k value. You can also use 0.9 to get the 90th percentile. The result of this formula tells me that 90% of the values in this dataset lies below 95.3 Also, note that you don’t need to have the data sorted for this formula to work. Sorting and giving you the final result is something PERCENTILE function automatically does in the backend. Similarly. in case you want to calculate the 50th percentile, you can use the formula below:
PERCENTILE.INC vs PERCENTILE.EXC – What’s the Difference?
Now, if you’re wondering why there are two separate percentile functions in Excel, let me try and explain. When you use PERCENTILE.INC function, it would calculate the result while including the first and last value in the dataset. And if you want to exclude the first and the last value from the calculation, you need to use the PERCENTILE.EXC function. In most cases, you will be required to use the PERCENTILE.INC function only. But since we are talking about the difference between the two functions, let me try and show you the difference with an example. Suppose you have the dataset as shown below where I have calculated different percentile values (in column C) using both PERCENTILE.INC and PERCENTILE.EXC. As you can see, apart from getting different results, the PERCENTILE.EXC function would return a #NUM! error when I try to calculate the percentile value for 0 or 100% In fact, PERCENTILE.EXC would give you an error for any value between:
0 and 1/(N+1) N/(N+1) and 1
where N is the total number of data points in the dataset (10 in this example) So it would give the NUM error for any Kth values that lie between 0 and 1/11 or 10/11 and 1. So it’s good to have the PERCENTILE.EXC function, but in most cases, you can just go ahead and use the PERCENTILE or the PERCENTILE.INC function. I hope you found this tutorial useful! Other Excel tutorials you may also like:
Calculate Percentage Change in Excel (% Increase/Decrease Formula)How to Make a Bell Curve in Excel (Step-by-step Guide)How to Calculate and Format Percentages in ExcelHow to Calculate Standard Deviation in Excel (Step-by-Step)How to Rank within Groups in ExcelHow to Get Descriptive Statistics in Excel?
