Percentile Rank Wise Conditional Formatting in Google Sheets

Welcome to the juicy secrets of percentile rank wise conditional formatting in Google Sheets! Get ready to learn a valuable tip that will make your day-to-day spreadsheet tasks a breeze.

Highlighting the Top Scorers

Imagine you’ve just conducted a written test, and you want to highlight the top 25% of scorers. With Google Sheets, you can create a custom conditional formatting formula using the Percentile function. This formula allows you to highlight values that fall within certain percentile ranges, such as the 0.25 percentile, 0.5 percentile, 0.75 percentile, and more.

Take a look at the screenshot below to see how it works:

In this example, the scores in column B are ranked based on percentile values. The score 991 in cell B2 is at the 100th percentile, 979 in B6 is at the 75th percentile, 928 in B10 is at the 50th percentile, and 820 in B14 is at the 25th percentile.

While Google Sheets has both the Percentile and Percentrank functions, the Percentile function is particularly useful for conditional formatting.

To calculate the percentile values, you can use the Array Formula in cell E5:

=ArrayFormula(percentile(B2:B19,G5:G8))

Note that this formula is not necessary for percentile rank wise conditional formatting; it’s just there for your information.

Now, let’s delve into the custom formula for percentile rank wise conditional formatting.

Custom Formula for Percentile Rank Wise Conditional Formatting

In the previous example, the different colors used represent different percentile ranks. Here’s a breakdown of the colors used:

• Dark Green: Values falling within the percentile rank between 0.75 to 1.
• Dark Cornflower Blue: Values falling within the percentile rank between 0.5 to 0.75.
• Dark Yellow: Values falling within the percentile rank between 0.25 to 0.5.
• Red: Values falling within the percentile rank between 0 to 0.25.

To apply these custom formulas in conditional formatting:

1. Formula 1:

   • Custom Formula: =$B2<=percentile(B$2:B$18,1)

2. Formula 2:

   • Custom Formula: =$B2<=percentile(B$2:B$18,0.75)

3. Formula 3:

   • Custom Formula: =$B2<=percentile(B$2:B$18,0.5)

4. Formula 4:

   • Custom Formula: =$B2<=percentile(B$2:B$18,0.25)

Remember to apply these formulas in the correct order, and change the “Formatting Style” for each rule accordingly.

Highlighting Percentile Values

Now, what if you only want to highlight the percentile values themselves, rather than the entire range? It’s simple, but there’s a trap you need to avoid.

To highlight a specific percentile, such as the 75th percentile, you can use the formula =PERCENTILE(B2:B18,0.75). However, when it comes to conditional formatting, you’ll need to modify the formula slightly.

Here’s how you can do it:

1. Go to “Format” > “Conditional formatting…”
2. Set the range to apply the formatting: B2:B18.
3. Use the following custom formula: =B2=PERCENTILE($B$2:$B$18,0.75)

This formula will highlight the value 979 in cell B6, which represents the 75th percentile. However, there’s a twist!

The above formula may not work in certain cases. Why? Because sometimes the value returned by the Percentile function may not be a member of the range B2:B18 due to interpolation.

To tackle this issue, follow these steps:

1. Ensure that the data is sorted in ascending order (from lowest to highest) based on column B.
2. Use the following formula with the Match function to determine the relative position of the percentile value in B2:B18: =match(PERCENTILE($B$2:$B$18,0.7),$B$2:$B$18,1)
3. Use the Index function with the row offset from the previous step to retrieve the percentile value: =index(B2:B18,match(PERCENTILE($B$2:$B$18,0.7),$B$2:$B$18,1))
4. For conditional formatting, use the following formula as the custom formula rule: =address(row(),column(),4)=("B"&match(PERCENTILE($B$2:$B$18,0.7),$B$2:$B$18,1)+1)

This formula will highlight the cell containing the percentile value when it’s not a member of the dataset.

That’s all you need to know about percentile rank wise conditional formatting in Google Sheets. Remember that the order of custom rules is crucial, so make sure to arrange them accordingly.

To further enhance your Google Sheets skills, check out these additional resources:

1. How to Highlight Vlookup Result Value in Google Sheets.
2. Find All the Cells Having Conditional Formatting in Google Sheets.
3. Highlight Intersecting Value in Google Sheets in a Two-Way Lookup.
4. Compare Two Google Sheets Cell by Cell and Highlight.

Visit Crawlan.com for more expert insights and tips on Google Sheets. Happy spreadsheet-ing!