More than top 10% in conditional formatting

[jonislindwert]

Hi,

I have a little problem with conditional formatting.

I want to have intervalls of the conditional formatting, not only top 10 or bottom 10 %.

Eg. I want all top 10% people to have green background, I want the following 11-35% best people to have light green, 36-65% yellow etc

As it is now I can only choose the top 10%, and the bottom 10%, but I want to have intervalls for those in between aswell. Is this possible to do?

This will be an monthly report so I cant use any fixed values, they need to be ranked of the total amount of persons, where the best 10% is green, the next 11-35% is light green etc.

Help please

 

[schielrn]

Welcome to the Board!

You could do it with a custom formula in the conditional formatting. The following formula will give you the top 10% and 11-35% and you can keep going. You will need to make sure that the 10% conditional format is first in the order of precedence and so on.

=A1>=LARGE($A:$A,INT(0.1*COUNT($A:$A)))
=A1>=LARGE($A:$A,INT(0.35*COUNT($A:$A)))

There may be a better way, but this is the first idea that came to my head.

Excel Workbook
	A
1	69
2	3
3	84
4	79
5	51
6	43
7	55
8	74
9	30
10	79
11	84
12	46
13	97
14	90
15	71
16	91
17	97
18	74
19	91
20	14
21	48
22	37
23	10
24	84
25	82
26	79
27	35
28	60
29	31
30	45
31	59
32	74
33	42
34	81
35	30
36	93
37	75
Sheet1

Note that if you have other numbers in the column that can't be counted in the percentile, you wouldn't be able to use the whole column A as in this example.