Excel is a beast when it comes to calculations. This means you can use it to calculate percentiles. If you are looking for a way to calculate Percentiles quickly, then this guide is for you.
First, I will take you through what a percentile means and then show you three functions you can use.
What is Percentile?
A percentile tells you how well you did compared to everyone else. For example, if you’re in the 75th percentile, it means you did better than 75% of the students. So, if there were 100 students, you’d be among the top 25. It’s a way to see where you stand in a group based on your performance.
Percentile Function in Excel
Excel has 3 variations of the percentile function.
PERCENTILE: This is the function that was mostly used in the older Excel versions. It’s still available for compatibility. However, the modern functions have been split to accommodate the two types of percentiles.
PERCENTILE.INC: In this function, if you got the exact same score as someone else, it still counts you both as that percentile. It includes you in the count. PERCENTILE.INC is the most used Percentile function in Excel
PERCENTILE.EXC: If you and someone else got the exact same score, you’re not both counted in that percentile. It excludes the shared score.
How to calculate percentile in Excel?
To calculate percentile, you need to use the following syntax.
=PERCENTILE.INC(array,k)
Array in this formula is the range of cells containing the values that you want to calculate its kth percentile.
On the other hand, K stands for the value between 0 and 1 that results in kth percentile value.. For instance, if you wish to find the 80th percentile, the k value is 0.8 or 80%. If you wish to find the 30th percentile, then the k value will be 0.3 or 30%
Here are the steps:
Assuming you wish to find the 60th percentile of the following data.
1. Open the spreadsheet containing the values you want to find values of
2. Type the following formula in an empty cell and press enter. For this example, I will use cell E5
=PERCENTILE.INC(B5:B14,60%)
As you can see from the image above, Excel has computed the 60th percentile automatically.
Download how to calculate percentile practice template
How do I calculate the 90th percentile in Excel?
To calculate the 90th percentile in Excel for given values. Type the formula =PERCENTILE.INC(Values,90%)in a new cell. Note that values can be actual values separated by a comma or a range of values, such as A2:A10. Press enter, and Excel will compute the 90th percentile of your values.
Alternatively, you can use the formula =PERCENTILE.INC(Values,0.9). If the values are in the range A2:A15, then the final formula should appear as =PERCENTILE.INC(A2:A15,0.9). Now press enter to get the results
How do I calculate the 95% percentile in Excel?
You can calculate the 95th percentile by using the following formula. Note that this formula assumes the values are in the range B5:B15. Therefore, the formula should be. =PERCENTILE.INC(B2:B15,95%)
How do I calculate the 75th percentile in Excel?
To calculate the 75th percentile in Excel, use any of the following formulas.=PERCENTILE.INC(B2:B14,75%) or =PERCENTILE.INC(B2:B14,0.75). Replace the range B2:B14 with the actual range of your data.
How do I calculate the 25th percentile in Excel?
You can calculate 25th percentile by using the formula =PERCENTILE.INC(B2:B15,25%) or =PERCENTILE.INC(B2:B15,0.25)
Similar Reads