Excel Formula for Calculating Percentiles (With Examples)
When working with data, one statistical metric that is frequently employed is percentile.
It provides you with a general understanding of a value’s location or ranks within the dataset.
In real life, I have observed the percentile value being applied to given scores in competitive exams, where you are given the percentile value. This informs you of your position about everyone else who took the exam.
I’ll cover all you need to know about Excel’s percentile function in this lesson, along with examples of how to compute the 90th and 50th percentiles.
Then let’s get going!
What is Percentile Covered in This Tutorial? A Simple Justification!
This instruction explains: 1. How does percentile work? A Simple Justification! 2. Excel's PERCENTILE Functions 3. Excel formula for 90th percentile (or 50th Percentile) 4. PERCENTILE. INC vs PERCENTILE EXC – What is the distinction?
MS EXCEL VBA TUTORIAL
1. How does percentile work? A Simple Justification!
The percentile value reveals a data point’s location within the whole dataset.
If I have the scores of 100 students, for instance, and I tell you that the 90th percentile score is 84, that indicates that anyone who receives an 84 would have a score higher than 90% of the other students.
Similarly to this, if a dataset’s 50th percentile value is 60, everybody with a score of 60 will have around 50% of individuals with higher scores and roughly 50% of people with lower scores.
This approach is favored since it conveys more significance than just providing the score.
If I tell you that your score is 90, for instance, it doesn’t inform you how you compare to the other students. However, if I told you that you scored in the 90th percentile, you would know right once that you performed better than 90% of the test-takers.
Excel has some built-in functions that make calculating the percentile value very simple.
2. Excel’s PERCENTILE Functions:
The percentile function in Excel comes in three different forms. All three of these functions are available if you’re using Excel 2010 or later versions.
THE PERCENTILE
This is an outdated function that is still available for backward compatibility. Although you could utilize them, it would be preferable to use the new ones (if you have those in your version of Excel). This function produces a number between 0 and 1.
PERCENTILE. INC –
This is the updated recipe (which works exactly like the PERCENTILE function). This is the function you should utilize the majority of the time. This function produces a number between 0 and 1.
PERCENTILE. EXC –
This operates similarly to the PERCENTILE. This INC function differs from the INC function in that it returns a value between 0 and 1, but eliminates K values between 0 and 1/(N+1) and N/(N+1) to 1. (where N is the size of the sample)
Simply expressed, in most circumstances, use PERCENTILE.INC (and if you are using Excel 2007 or prior versions, use the PERCENTILE function)
The Excel PERCENTILE.INC function’s syntax is as follows:
=PERCENTILE.INC(array,k)
where:
1. array:
is the set of cells where the values for which you want to determine the K-th percentile are present.
2. k:
provides you the k-th percentile value and has a value between 0 and 1. For instance, to calculate the 90th percentile value, enter 0.9 or 90%, and to compute the 50th percentile value, enter 0.5 or 50%.
The PERCENTILE and PERCENTILE.EXC functions have identical syntax.
3. Excel formula for 90th percentile (or 50th Percentile)
Assume you have the dataset depicted below and are interested in learning what the dataset’s 90th percentile value is.
The calculation to determine your 90th percentile is given below:
=PERCENTILE.INC(A2:A21,90%)
90% is what I’ve used as the k value in the calculation above. To calculate the 90th percentile, you may alternatively use 0.9.
This formula’s output indicates that 90% of the values in this dataset are lower than 95,3.
Additionally, keep in mind that the data does not need to be sorted for this formula to function. The PERCENTILE function automatically sorts the results and provides them to you in the backend.
Similar to that, you may use the following formula to determine the 50th percentile:
=PERCENTILE.INC(A2:A21,50%)
MS EXCEL VBA TUTORIAL
4. PERCENTILE. INC vs PERCENTILE EXC – What is the distinction?
Now, if you’re wondering why Excel has two different percentile functions, allow me to attempt an explanation.
The earliest and last values in the dataset are taken into account when using the PERCENTILE.INC function to determine the outcome. Additionally, you must use the PERCENTILE.EXC function to remove the first and last values from the calculation.
You will often be limited to using the PERCENTILE.INC function.
But given that we are discussing the distinctions between the two duties, allow me to attempt to illustrate them with an illustration.
Assume you have the dataset as displayed below, where I have used both PERCENTILE to generate various percentile values (in column C).
PERCENTILE and INC. EXC.
As you can see, when I try to calculate the percentile value for 0 or 100%, the PERCENTILE.EXC function would return a #NUM! error in addition to producing different results.
Actually, PERCENTILE Any value in this range would result in an error from EXC:
where N is the total number of data points in the dataset,
- 0 and 1/(N+1),
- N/(N+1), and 1
(10 in this example)
Therefore, any Kth numbers between 0 and 1/11 or 10/11 and 1 would result in the NUM problem.
Although it’s useful to have the PERCENTILE.EXC function, you can usually get by with using the PERCENTILE or PERCENTILE.INC function instead.
