I am quite confident that you will find this percentile rank wise conditional formatting tips very useful. You can probably use this in your day-to-day spreadsheet tasks in Google Sheets.
Here is one example to the percentile rank wise conditional formatting.
In a written test if your percent rank is 0.75, that means you are better than 75 percent of the other applicants who have written the test. In concise you did better than 75% of the other applicants.
How to highlight that top 25% of scorers?
A custom conditional formatting formula using the Percentile function as the key function can highlight a value that falls at certain percentile like 0.25 percentile, 0.5 percentile, 0.75 percentile and so on.
Also, you can highlight rows between certain percent ranks like the percentile values fall in the percentile rank between 0.5 to 0.75. See the below screenshot.
In the above example, 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.
Though there are Percentile and Percentrank functions in Google Docs Sheets, the former function come in handy in conditional formatting.
In cell E5 I have used the below Array Formula to return the above percentile values.
=ArrayFormula(percentile(B2:B19,G5:G8))The above formula is not required for percentile rank wise conditional formatting in Google Sheets. I have kept it there just for your info.
Then how to highlight values based on Percentile Rank in Google Sheets?
Custom Formula for Percentile Rank Wise Conditional Formatting
First understand the colors that I have used in the above example.
The Rows Highlighted in Dark Green Color: The values in column B that falls in the percentile rank between 0.75 to 1 (percent ranks are assigned from 0 to 1).
Dark Cornflower Blue: Represents percentile rank between 0.5 to 0.75.
Dark yellow color represents percentile rank between 0.25 to 0.5.
Red: Represents percentile rank between 0 to 0.25.
Now you must apply four custom formulas in the conditional formatting. See that four custom formulas for percentile rank wise conditional formatting in Sheets.
Formula 1:
=$B2<=percentile(B$2:$B$18,1)Formula 2:
=$B2<=percentile(B$2:$B$18,0.75)Note: The above two custom conditional formatting formulas and the following two formulas are as per the above color coding order.
Formula 3:
=$B2<=percentile(B$2:$B$18,0.5)Formula 4:
=$B2<=percentile(B$2:$B$18,0.25)Now you have the formulas in hand. How to apply these formulas?
Conditional formatting is related to formatting of Sheets. That means you can find the option “Conditional formatting…” under the Format menu.
Here is the setting that you must follow.
This setting is for the above dark green color formula (top percentile ranks) and range. Once “Done” click on “Add another rule”.
Follow the above setting. Add all the formulas one by one. Only change the “Formatting style” in each rule.
Once finished the custom rules must be in the following order. If not, drag and drop the rules.
That’s all about the percentile rank wise conditional formatting in Google Sheets.
Note: In the above example, I have kept the values (scores) in descending order. Actually, it’s not a must.
Additional Tips:
I just want to highlight the percentile values. Not all the values that fall in certain percentage ranks’ range. How to do that?
Seems simple, right? Yes! It’s simple, but there is a ‘trap’. Let me detail that under the new title below.
How to Highlight Percentile in Google Sheets
Compared to percent ranks, it’s pretty easy to highlight percentile in Google Sheets.
I want to highlight the 75th Percentile in the above same data. Here is the formula that returns the 75the Percentile.
=PERCENTILE(B2:B18,0.75)But in conditional formatting use the custom formula as below.
Format > Conditional formatting…
Apply to range: B2:B18
Format Rules > Format cells if… > Custom formula is;
=B2=PERCENTILE($B$2:$B$18,0.75)This will highlight the value 979 in cell B6. But there is a twist!
The above formula may not work all times. Do you know why?
This is because sometimes the value returned by the Percentile function may not be a member of the range B2
For example, see this formula.
=PERCENTILE($B$2:$B$18,0.7)It returns the value 947.8 which is not a member of the range B2
The solution is to make the non-member value a member value.
Highlight Percentile When It’s Not a Member of Dataset
Make sure that the data is sorted (sort by column B) in ascending order (lowest to highest).
Use the above formula in the Match to return the relative position of the percentile value in B2:B18.
=match(PERCENTILE($B$2:$B$18,0.7),$B$2:$B$18,1)Use this as the row offset in Index.
=index(B2:B18,match(PERCENTILE($B$2:$B$18,0.7),$B$2:$B$18,1))This will return the value 940 which is 70th Percentile and a member in the dataset. But this won’t serve in the conditional formatting!
There instead of using the Index function use the below formula as the custom formula rule for highlighting.
=address(row(),column(),4)=("B"&match(PERCENTILE($B$2:$B$18,0.7),$B$2:$B$18,1)+1)The second part of the formula that starts from “B” returns the cell address $B$13 of the percentile value.
The Address function with the Row and Column functions as the arguments in it returns current cell address. When both matches, that cell got highlighted.
The +1 at the last part of the formula indicates the number of rows above B2:B18.
Additional Resources:
