Do you want to calculate the percentile for each group in Google Sheets? Although there is no dedicated function for this, don’t worry! We can still achieve it by combining other functions. In this article, I’ll show you how to do it step by step.
Why Calculate Group-Wise Percentile?
Let’s say you have a table that contains marks of students in different grades. If you want to calculate the 75th percentile of all the marks, you can simply use the PERCENTILE function like this:
Syntax: =percentile(all_marks,0.75)
But what if you want to calculate the percentile for each grade separately? This is where the function alone won’t work. However, we can use a combination of FILTER or IF with PERCENTILE to accomplish this.
Before we dive into an example, let me give you some more details about the PERCENTILE function. Compared to other statistical functions like AVERAGE, MIN, MAX, and MEDIAN, PERCENTILE may not be as commonly used. So, it’s important to have a good understanding of how it works.
In a numerical dataset, the value returned by the MEDIAN function is equal to the 50th percentile. For example, if you find the median of the numbers 5 and 6, the result will be 5.5. Similarly, the 50th percentile of these numbers will also be 5.5. Keep in mind that both functions interpolate to determine the value at the 50th percentile.
How to Calculate k-th Percentile for Each Group in Google Sheets
To calculate the k-th percentile for each group in Google Sheets, follow these steps:
-
Get the unique values of the groups (grades in this case) by using the UNIQUE formula. Enter the following formula in cell E3:
=sort(unique(B2:B)) -
Calculate the percentile for each group using the combination of FILTER and PERCENTILE functions. Enter the following formula in cell F3 and drag it down to F4 and F5:
=percentile(filter($C$2:$C,$B$2:$B=E3),0.6)
Alternatively, you can use the combination of IF and PERCENTILE functions like this: =ArrayFormula(percentile(if($B$2:$B=E3,$C$2:$C),0.6))
By using these formulas, you will be able to calculate the desired percentile for each group.
Creating a Percentile Column
If you want to calculate the percentile for each group in a new column for all the rows, follow these steps:
- Modify the FILTER formula criterion to use the condition from the table itself. Enter the following formula in cell D2:
=percentile(filter($C$2:$C,$B$2:$B=B2),0.6)
In this example, the FILTER function uses the condition from column B to filter the marks and calculate the percentile. You don’t need to sort the data for this calculation.
That’s it! Now you know how to calculate the percentile for each group in Google Sheets. It’s a powerful technique that can help you analyze data more effectively.
If you want to learn more about percentile rank-wise conditional formatting and the PERCENTRANK functions in Google Sheets, check out the resources below:
- Percentile Rank Wise Conditional Formatting in Google Sheets
- The PERCENTRANK Functions in Google Sheets
Thank you for reading, and enjoy exploring the possibilities of Google Sheets!
