In this article, we will learn how to calculate percentile in excel but before that, lets define percentile. Percentile is a value on a scale of 100 that indicates the percent of a distribution that is equal to or below it.
Percentiles are useful as they can tell you how one value compares to other values in the data set.
Formula to Calculate Percentile in Excel.
Just like other functions, the percentile function can be found in excel.
There are three types of percentile functions in excel;
- =PERCENTILE. This one returns the kth percentile of values in a range.
- =PERCENTILE.EXC This one returns the kth percentile of values in a range, where k is in the range 0..1, exclusive.
- PERCENTILE.INC. This one returns the kth percentile of values in a range, where k is in the range 0..1, inclusive.
Example:
Calculate the 90th percentile of the following students scores in mathematics.
| Name | Score out of 100. |
| Mike | 70 |
| Archie | 30 |
| Hermione | 40 |
| Hiram | 50 |
| Becky | 91 |
Therefore, we begin by transferring the data to an excel spreadsheet.
We are going to use =PERCENTILE function to get the 90th percentile.
Since the k value in excel runs form 0 to 1, we are going to convert 90% to 0.9.
Array is the number of values you are going to calculate percentile from.
Therefore, this means that 90% of the scores are lower or equal to 82.6.
