*53*

The n^{th} **percentile** of a dataset is the value that cuts off the first *n* percent of the data values when all of the values are sorted from least to greatest.

For example, the 90th percentile of a dataset is the value that cuts of the bottom 90% of the data values from the top 10% of data values.

There are three different functions you can use to calculate percentiles in Excel:

**1. PERCENTILE.EXC:** This function returns the k^{th} percentile of a dataset, **excluding** the values 0 and 1.

**2. PERCENTILE.INC:** This function returns the k^{th} percentile of a dataset, **including** the values 0 and 1.

**3. PERCENTILE:** This function returns the k^{th} percentile of a dataset as well. It will return the exact same value as the **PERCENTILE.INC** function.

The following example shows how to use the various PERCENTILE functions in Excel.

**Example: PERCENTILE.EXC vs. PERCENTILE.INC in Excel**

Suppose we have the following dataset in Excel:

The following screenshot shows how to calculate the 20^{th} percentile for the dataset using the three different percentile formulas:

Using the **PERCENTILE **or **PERCENTILE.INC** functions, we calculate the 20^{th} percentile to be 6.

Using the **PERCENTILE.EXC** function we calculate the 20^{th} percentile to be 5.4.

**When to Use PERCENTILE.EXC vs. PERCENTILE.INC**

In almost all cases, it makes more sense to use the **PERCENTILE.INC** function because this function includes the values 0 and 1 when calculating the percentiles.

It’s also worth nothing that both the R programming language and the Python programming language use formulas to calculate percentiles that match the **PERCENTILE.INC** function in Excel.

The following tutorials explain how to calculate the percentiles of a dataset in both R and Python:

No matter which function you use to calculate percentiles, the difference between the values calculated by **PERCENTILE.INC** and **PERCENTILE.EXC** will be very similar in most cases.

In some cases, it’s even possible that the two functions will return the same values depending on the sequence of numbers in the dataset.

**Additional Resources**

STDEV.P vs. STDEV.S in Excel: What’s the Difference?

VAR.P vs. VAR.S in Excel: What’s the Difference?

Percentile vs. Quartile vs. Quantile: What’s the Difference?