Quantile (Quartile, Decile and Percentile): Manual Calculation + Microsoft Excel

• /
• Blog
• /
• Quantile (Quartile, Decile and Percentile): Manual Calculation + Microsoft Excel

Quantiles are an important statistical concept that allows us to divide data into equal groups. They are often used to identify and analyze data patterns and make meaningful comparisons between different datasets. In this quick guide, we'll cover the basics of quantiles and take a closer look at some of the most common types: quartiles, deciles, and percentiles.

Quantile

A quantile is a measure that indicates the value below which a certain proportion of observations in a group of observations fall. Quantiles are used in statistics to divide a group of observations into equal-sized groups. For example, the 0.25 quantile is the value below which 25% of the observations fall; the 0.50 quantile is the value below which 50% falls, and so on. Another related measurement is the Median, which is the same as the 0.50 quantile because 50% of data fall below the median.

What are some common quantiles?

Some common quantiles include:

1. Quartile

A quartile is a type of quantile that divides a group of observations into four equal-sized groups. For example, in a group of observations, the first quartile (Q1) is the value below which the first 25% of the observations fall, the second quartile (Q2, also known as the median) is the value below which the middle 50% of the observations fall, and the third quartile (Q3) is the value below which the last 25% of the observations fall.

2. Decile

A decile is a measure that divides a group of observations into ten equal-sized groups. For example, in a group of observations, the first decile (D1) is the value below which the first 10% of the observations fall, the second decile (D2) is the value below which the first 20% of the observations fall, and so on. The 9th decile (D9) is the value below which the last 10% of the observations fall.

3. Percentile

A percentile is a measure that indicates the value below which a certain percentage of observations in a group of observations fall. For example, in a group of observations, the 20th percentile (P20) is the value below which the first 20% of the observations fall, the 50th percentile (P50) is the value below which the middle 50% of the observations fall, and the 95th percentile (P95) is the value below which the last 95% of the observations fall.

The 50th percentile is also the median, second quartile, and 5th decile.

Percentile: Manual / Microsoft Excel Calculation

A percentile is a measure used in statistics to indicate the value below which a certain percentage of observations falls in a group of observations.

To find the location of a specific percentile, software such as Minitab, Python, R, and Excel uses these steps:

1. Arrange the observations in ascending order.
2. Use the formula for percentile location to calculate the position where the percentile value would be located, using the desired percentile value and the total number of observations as inputs. There are two approaches: EXC (Exclusive) and INC (Inclusive). The percentile location in the EXC approach is given by the formula $$K(N+1)$$, and the location in the INC approach is given by the formula $$K(N-1)+1$$.
3. If the percentile location is a whole number, the value at that position in the ordered list of observations is the percentile value.
4. If the percentile location is not a whole number, the percentile value is calculated by calculating the value on a pro-rata basis between these two numbers.

To find the 65th percentile in a group of 8 observations, you would first arrange the observations in ascending order: 8, 9, 12, 22, 23, 33, 55, 61.

Next, you would use the formula for percentile location to calculate the position where the 65th percentile would be located:

For PERCENTILE.EXC, the calculated rank is $$K(N+1)$$.

Percentile location (Using Exclusive Approach) = $$\left(\frac{65}{100}\right)(8+1)$$ = 5.85

Since the percentile location is not a whole number, the 65th percentile would be between the 5th item (number 23)and the 6th item (number 33) on a pro-rata basis. This will be $$23+0.85(33-23) = 31.5$$.

For PERCENTILE.INC (and PERCENTILE), the calculated rank is $$K(N-1)+1$$.

Percentile location (Using Inclusive Approach) = $$(65/100) (8-1)+1$$ = 5.55

Since the percentile location is not a whole number, the 65th percentile would be almost in the middle of the 5th item (number 23)and the 6th item (number 33). On a pro-rata basis, this will come out to be $$23+0.55(33-23)$$ = 28.5.

Percentile Calculator App

Summary

In general, to find the p quantile, you would arrange the observations in ascending order and use the formula for percentile location to calculate the position where the p quantile would be located. If the percentile location is a whole number, the p quantile is the value at that position in the ordered list of observations. If the percentile location is not a whole number, the p quantile is calculated on pro-rata basis.

Negative Team Dynamics

49 Courses on SALE!