Conditional formatting with percentiles

Hi,
I need to conditionally format a row of values based on percentiles - but i need four groupings and need four distinct colours (not scale colours).
The groupings are:
Blue - top 15% of values - i.e. 85th percentile and up
Green - the next 50% of values - i.e. 36th - 85th percentile
Amber - the next 25% of values - i.e. 11th - 35th percentile
Red - the bottom 10% of values - i.e. 10th percentile and below

(fyi i didn't choose these groupings!)

I tried using the colour scales method but can't have 4 scales and plus they don't want the shading - just those 4 colours.
I've ended up using the top and bottom ranked function and making four rules with this - which are (in order):
Bottom 10%
Bottom 35%
Top 15%
Top 65%

It seems to work but one value is left white and i can't work out why (have attached it). I've tried changing the 35% and 65% to see if it was just on the cusp but even going up and down by 3% doesn't do it.

Any help on this or a new way of doing it?

Hi sairg,

You are really close...
Use these rules instead in this order...
Top 15% (blue)
Top 65% (green)
Top 90% (orange)
Top 100% (red)

Stick to one type of rule (all Top or all Bottom), don't mix them up...

Amazing!! You're a star, thanks so much!
My spreadsheet actually has many more rows which i need to do the same for. I don't suppose you know how i paste that conditional formatting down (but have it work on a row by row basis)?

Thanks again,

Sarah

Originally Posted by djapigo

Hi sairg,

You are really close...
Use these rules instead in this order...
Top 15% (blue)
Top 65% (green)
Top 90% (orange)
Top 100% (red)

Stick to one type of rule (all Top or all Bottom), don't mix them up...

Glad it worked...

To copy to the other rows... you have to be careful because I'm assuming you only want the formatting per row, correct? If so, highlight the first row or range, CTRL-C to copy, then use the Format Painter to the next row... one row at a time! If you choose a bunch of rows, then the conditional formatting will assume the ranges in the conditional formatting will apply to all the cells in all the rows... unless that's what you want...