Changing formatting for a percentile-based Color Scale

[Ezzard]

I have a list of numerical data and I want to use conditional formatting to highlight certain cells based on their percentile within the data. For the top 15% I want to color the data in one color and for the bottom 15% I want to color the data in another color. Any data that is in between should not have its formatting changed at all. I have tried using the color scale to format this data, but when I do so, the cells in between the data are scaled between the two colors for the top and bottom percentiles.

How do I conditionally format the top and bottom percentiles without formatting the rest of the data at all?

 

[bbott]

Assuming a data set in A1:A10, maybe these CF formulas would work for you?

=A1 >= PERCENTILE($A$1:$A$10,0.85)

=A1 <= PERCENTILE($A$1:$A$10,0.15)