Table of Contents
To calculate the 90th percentile in Excel, you need to first enter the data set into a spreadsheet. Once the data is entered, you can use the PERCENTILE.EXC function to calculate the 90th percentile. This function takes two arguments, the array of data values and the percentile to be calculated. When you enter the arguments and press enter, the function will return the value at the 90th percentile.
The 90th percentile of a dataset is the value that cuts off the bottom 90 percent of the data values from the top 10 percent of data values when all of the values are sorted from least to greatest.
To find the 90th percentile of a dataset in Excel, you can use one of the following two functions:
- =PERCENTILE(array, k)
- =PERCENTILE.INC(array, k)
Both functions will return the same value. For both functions, the array is the list of values in your dataset and k is the percentile you’d like to find between 0 and 1.
To find the 90th percentile, we will use 0.9 for k.
Note that there is also a function called =PERCENTILE.EXC that calculates percentiles between 0 and 1, exclusive. This function is rarely used in practice.
The following example shows how to calculate the 90th percentile of a dataset in Excel.
Example: Calculating the 90th Percentile in Excel
Suppose we have the following dataset that shows the final exam scores of 20 students in a particular class:
We can use the following syntax to find the 90th percentile of the exam scores:
The 90th percentile turns out to be 94.1.
This is the score that a student must receive in order to have a score that is greater than 90% of the exam scores in the entire class.
Notes
Keep in mind the following notes when calculating percentiles in Excel:
- The value for k must always be between 0 and 1.
- The percentile function will display a #VALUE! Error if you enter a non-numeric value for k.
- The data in our example was sorted from lowest to highest exam scores, but a dataset does not need to be pre-sorted in this manner for the percentile function to work.