Conditional formatting problem

[dhaynes]

I am having a conditional formatting issue that I can not seem to solve. The formatting for column with the $ data is working correctly because I simply want to rank and highlight the three highest values.

However, for the column with the % data, I want to rank and highlight the three lowest values, excluding anything equal or less than 0. In other words, I would like to highlight the top three values in the column with the % data (.5%, .5% and 1%).

Here are the functions I have written:
Rule 1: Cells <= 0 fill cell white

Rule 2: =AND(ISNUMBER(C2), COUNTIF(C2:C2,C16)=1, C2=SMALL(IFERROR(1/SIGN(TRANSPOSE(FREQUENCY(MATCH(C2:C16&"",C2:C16&"",0),MATCH(C2:C16&"",C2:C16&"",0)))*ISNUMBER(C2:C16))*C2:C16,FALSE), 1))..fill cell green

Rule 3: =AND(ISNUMBER(C2), COUNTIF(C2:C2,C16)=1, C2=SMALL(IFERROR(1/SIGN(TRANSPOSE(FREQUENCY(MATCH(C2:C16&"",C2:C16&"",0),MATCH(C2:C16&"",C2:C16&"",0)))*ISNUMBER(C2:C16))*C2:C16,FALSE), 2))...fill cell yellow

Rule 4: =AND(ISNUMBER(C2), COUNTIF(C2:C2,C16)=1, C2=SMALL(IFERROR(1/SIGN(TRANSPOSE(FREQUENCY(MATCH(C2:C16&"",C2:C16&"",0),MATCH(C2:C16&"",C2:C16&"",0)))*ISNUMBER(C2:C16))*C2:C16,FALSE), 3))..fill cell red


I have a feeling I may be making this more complicated than need be, but if anyone knows if a solution to this issue, I would be grateful.

 

[jasonb75]

I haven't tested this because screen captures can't be copied to excel without retyping the data (xl2bb captures can )

If my thinking is correct, this should work for the lowest value greater than zero,

=(COUNTIFS(C$2:C$16,">0",C$2:C$16,"<"&C2)+COUNTIFS(C$2:C2,">0",C$2:C2,C2))=1

Changing =1 at the end to =2 and =3 should work for the others.

 

[dhaynes]

Thanks Jasonb75, not only did that work but it is much easier than the path I was going down. You are the man! Having said that, one more requirement comes to mind...can you think of a way to format two cells with the same value. For example, I would like .5% to be formatted with the same color and .75% would be another color. Thank you very much!

Sample data.xlsx
	B	C	D
1	Company	%	$
2	A	0.50%	$100.000
3	B	0.50%	$100.063
4	C	1.00%	$100.094
5	D	0.75%	$100.125
6	E	2.00%	$100.125
7	F	2.25%	$100.156
8	G	2.50%	$100.188
9	H	2.75%	$100.219
10	I	3.00%	$100.250
11	J	3.25%	$100.281
12	K	3.50%	$100.313
13	L	0.00%	$ -
14	M	3.75%	$100.406
15	N	4.00%	$100.438
16	O	4.25%	$100.469
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C16	Expression	=(COUNTIFS(C$2:C$16,">0",C$2:C$16,"<"&C2)+COUNTIFS(C$2:C2,">0",C$2:C2,C2))=3	text	NO
C2:C16	Expression	=(COUNTIFS(C$2:C$16,">0",C$2:C$16,"<"&C2)+COUNTIFS(C$2:C2,">0",C$2:C2,C2))=2	text	NO
C2:C16	Expression	=(COUNTIFS(C$2:C$16,">0",C$2:C$16,"<"&C2)+COUNTIFS(C$2:C2,">0",C$2:C2,C2))=1	text	NO
D2:D16	Expression	=3=RANK(D2,$D$2:$D$16)	text	NO
D2:D16	Expression	=2=RANK(D2,$D$2:$D$16)	text	NO
D2:D16	Expression	=1=RANK(D2,$D$2:$D$16)	text	NO

 

[jasonb75]

What would be the correct colour pattern when there are duplicates?

Green, green, yellow? Green, green red? Yellow, yellow, red? Something else?

What if the results were 0.50%, 0.50%, 0.75%, 0.75%, 0.75%? how would you want to format them?

 

[dhaynes]

If the highest value in the column with the $ data is formatted is duplicated, all duplicated values will be filled with green. Ideallly the same thing would occur with the column containing the % data with the exception that the lowest number, including any duplicated, would be filled with green. The lowest number would be highlighted yellow and the third red.
If the results were as the suggest, I would want all of the .5% filled with green and all the .75% filled with yellow.

 

[jasonb75]

See if this one works for the percentages, change the =1 at the end as before.

=SUMPRODUCT((D2>=$D$2:$D$16)/COUNTIF($D$2:$D$16,$D$2:$D$16))=1

You should be able to use the same for the $ amounts by changing > to <

 

[dhaynes]

Thanks, that worked with the exception that it now removed the >0 requirement but I should be able to work that back in

 

[jasonb75]

Oops, I thought I had forgotten something

=SUMPRODUCT((A2>=$A$2:$A$16)*($A$2:$A$16>0)/COUNTIF($A$2:$A$16,$A$2:$A$16))

 

[dhaynes]

Thank you very much for all your help!