How to Use PERCENTILES in Excel

Excel is a powerful tool for working with data, and one of the features that can help you analyze and understand your data better is the PERCENTILE function. In this article, you will learn what percentiles are, how to calculate them in Excel, and what are the differences between the various PERCENTILE functions available in Excel.

What are Percentiles?

A percentile is a value that indicates the relative position of a data point in a data set. For example, if you have the scores of 100 students in a test, and you want to know how well you did compared to the others, you can use the percentile value of your score.

The percentile value tells you the percentage of values in the data set that are less than or equal to your value. For example, if your score is 80 and your percentile value is 75, it means that 75% of the scores in the data set are less than or equal to 80. In other words, you scored better than 75% of the students who took the test.

You can also use percentiles to divide a data set into groups. For example, you can use the 25th percentile, the 50th percentile (also known as the median), and the 75th percentile to divide a data set into four equal groups, called quartiles. The first quartile contains the lowest 25% of the values, the second quartile contains the next 25%, and so on.

Percentiles can help you understand the distribution and variability of your data, as well as identify outliers and extreme values. For example, if you have a data set with a large gap between the 75th percentile and the maximum value, it means that there are some very high values that skew the data. Similarly, if you have a data set with a small gap between the 25th percentile and the minimum value, it means that there are some very low values that drag down the data.

How to Calculate Percentiles in Excel

Excel has three functions that can help you calculate percentiles in Excel: PERCENTILE, PERCENTILE.INC, and PERCENTILE.EXC. These functions have slightly different syntaxes and behaviors, so let’s look at each one in detail.

PERCENTILE Function

The PERCENTILE function is an old function that is kept for backward compatibility purposes. It works exactly like the PERCENTILE.INC function, which we will discuss next. The syntax of the PERCENTILE function is:

=PERCENTILE(array,k)

where:

array is the range of cells where you have the values for which you want to find out the k-th percentile.
k is the percentile value in the range 0 to 1, inclusive.

For example, if you have a list of scores in cells A2:A101, and you want to find out the 90th percentile of these scores, you can use this formula:

=PERCENTILE(A2:A101,0.9)

This will return 94.2, which means that 90% of the scores are less than or equal to 94.2.

PERCENTILE.INC Function

The PERCENTILE.INC function is a new function that was introduced in Excel 2010. It works exactly like the PERCENTILE function, so you can use either one of them. The syntax of the PERCENTILE.INC function is:

=PERCENTILE.INC(array,k)

where:

array is the range of cells where you have the values for which you want to find out the k-th percentile.
k is the percentile value in the range 0 to 1, inclusive.

For example, if you have a list of scores in cells A2:A101, and you want to find out the median (or 50th percentile) of these scores, you can use this formula:

=PERCENTILE.INC(A2:A101,0.5)

This will return 78.5, which means that half of the scores are less than or equal to 78.5.

PERCENTILE.EXC Function

The PERCENTILE.EXC function is another new function that was introduced in Excel 2010. It works slightly differently from the PERCENTILE and PERCENTILE.INC functions. The syntax of the PERCENTILE.EXC function is:

=PERCENTILE.EXC(array,k)

where:

array is the range of cells where you have the values for which you want to find out the k-th percentile.
k is the percentile value in the range 0 to 1, exclusive.

The difference between PERCENTILE.EXC and PERCENTILE.INC is that PERCENTILE.EXC excludes some values from being possible percentiles. Specifically, it excludes k values between 0 and 1/ (n + 1) as well as n/ (n + 1) and 1, where n is the size of the sample.

For example, if you have a list of 10 scores in cells A2:A11, and you want to find out the 90th percentile of these scores using PERCENTILE.EXC, you can use this formula:

=PERCENTILE.EXC(A2:A11,0.9)

This will return 97.8, which means that 90% of the scores are less than 97.8.

However, if you try to use k values such as 0, 0.1, or 1, you will get an error. This is because these values are not valid for PERCENTILE.EXC. The smallest possible k value for PERCENTILE.EXC is 1/ (n + 1), which in this case is 1/11 or 0.0909. The largest possible k value for PERCENTILE.EXC is n/ (n + 1), which in this case is 10/11 or 0.9091.

The reason for this behavior is that PERCENTILE.EXC uses a different formula to calculate the percentiles than PERCENTILE.INC. PERCENTILE.EXC uses the following formula:

=IF(k < 1/ (n + 1),#NUM!,IF(k > n/ (n + 1),#NUM!,x[m] + (k - m/ (n + 1)) * (x[m+1] - x[m])))

where:

n is the size of the sample
k is the percentile value in the range 0 to 1, exclusive
x[m] is the m-th smallest value in the data set
m is the integer part of k * (n + 1)

This formula ensures that the percentiles are always within the range of the data set, and that there are exactly n - 1 possible percentiles.

PERCENTILE.INC, on the other hand, uses the following formula:

=IF(k < 0,#NUM!,IF(k > 1,#NUM!,x[m] + k * (x[m+1] - x[m])))

where:

n is the size of the sample
k is the percentile value in the range 0 to 1, inclusive
x[m] is the m-th smallest value in the data set
m is the integer part of (k * n) - k

This formula allows for k values of 0 and 1, which correspond to the minimum and maximum values in the data set, respectively. It also allows for more possible percentiles than PERCENTILE.EXC.

Which PERCENTILE Function to Use?

In most cases, you can use either PERCENTILE or PERCENTILE.INC to calculate percentiles in Excel. These functions are more widely used and accepted than PERCENTILE.EXC. However, if you need to follow a specific statistical method or standard that requires PERCENTILE.EXC, then you can use that function instead.

The choice of which PERCENTILE function to use may also depend on your data set and what you want to achieve with it. For example, if you have a small data set with few values, you may prefer to use PERCENTILE.EXC to avoid getting the minimum or maximum values as percentiles. On the other hand, if you have a large data set with many values, you may prefer to use PERCENTILE.INC to get more granularity and precision in your percentiles.

In any case, you should always check your results and make sure they make sense for your data and your analysis.

Summary

In this article, you learned how to use percentiles in Excel to analyze and understand your data better. You learned what percentiles are, how to calculate them in Excel using three different functions: PERCENTILE, PERCENTILE.INC, and PERCENTILE.EXC. You also learned what are the differences between these functions and how to choose which one to use depending on your data and your needs.

We hope you found this article helpful and informative. If you have any questions or feedback, please feel free to leave a comment below.

How to Use PERCENTILES in Excel