decision formula/ conditional formatting

[nature969]

HI

Ihave a set of data (see below). I want to count “YES” and the more “yes”supplier have the darker colour the result it should be ( in this way I canshow which supplier I should pick)

Icouldn’t figure out a way. The only thing I can think about is percentage.
Cananyone help me to find a way?


[TABLE="width: 184"]
<colgroup><col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="64" style="width: 48pt;"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <tbody>[TR]
[TD="width: 82, bgcolor: transparent"]supplier 1[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 98, bgcolor: transparent"]supplier 2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yes[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]NO[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yes[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Yes
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yes[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]71%[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: right"]86%[/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

You could use helper columns to count "yes", and then Conditional Format the counting columns with Data Bars?

Book1
	A	B	C	D
1	supplier 1		supplier 2
2	yes	1	NO	0
3	No	1	Yes	1
4	Yes	2	Yes	2
5	yes	3	Yes	3
6	No	3	Yes	4
7	Yes	4	Yes	5
8	yes	5	Yes	6
Sheet1

Cell Formulas
Range	Formula
B2	=COUNTIF($A$2:A2,"YES")
D2	=COUNTIF($C$2:C2,"YES")

 

[Peter_SSs]

Another suggestion. For my sample data ..
Insert a new row 1 fill with a formula as shown, copied across. You can hide this row after inserting the formulas if you want.
Select A2:E12 and set up the Conditional Formatting (formulas) as shown.

Note that after setting up the formulas, Go back in to 'Manage' the CF and ensure they are in the right order. The right order for me is
Dark green for the supplier with the most 'Yes' values.
Light green for 2nd most
Yellow for 3rd (note there is no yellow in my first table as there are 2 equal 2nd best, both light green.
Blue for 4th best.

Excel Workbook
	A	B	C	D	E
1	5	3	2	5	6
2	Supp 1	Supp 2	Supp 3	Supp 4	Supp 5
3	No	No	No	Yes	Yes
4	No	Yes	No	No	No
5	Yes	Yes	No	No	Yes
6	No	No	Yes	No	Yes
7	No	No	Yes	No	Yes
8	Yes	No	No	Yes	Yes
9	No	No	No	No	No
10	Yes	No	No	Yes	No
11	Yes	No	No	Yes	No
12	Yes	Yes	No	Yes	Yes
Best Suppliers

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =A$1=LARGE($A$1:$E$1,1)	Abc
A2	2. / Formula is =A$1=LARGE($A$1:$E$1,2)	Abc
A2	3. / Formula is =A$1=LARGE($A$1:$E$1,3)	Abc
A2	4. / Formula is =A$1=LARGE($A$1:$E$1,4)	Abc

Here is my sheet again with some different data.

Excel Workbook
	A	B	C	D	E
1	5	3	2	7	4
2	Supp 1	Supp 2	Supp 3	Supp 4	Supp 5
3	No	No	No	Yes	Yes
4	No	Yes	No	No	No
5	Yes	Yes	No	Yes	No
6	No	No	Yes	Yes	Yes
7	No	No	Yes	No	Yes
8	Yes	No	No	Yes	No
9	No	No	No	No	No
10	Yes	No	No	Yes	No
11	Yes	No	No	Yes	No
12	Yes	Yes	No	Yes	Yes
Best Suppliers

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =A$1=LARGE($A$1:$E$1,1)	Abc
A2	2. / Formula is =A$1=LARGE($A$1:$E$1,2)	Abc
A2	3. / Formula is =A$1=LARGE($A$1:$E$1,3)	Abc
A2	4. / Formula is =A$1=LARGE($A$1:$E$1,4)	Abc

 

[nature969]

You could use helper columns to count "yes", and then Conditional Format the counting columns with Data Bars?

	A	B	C	D
supplier 1	supplier 2
yes	NO
No	Yes
Yes	Yes
yes	Yes
No	Yes
Yes	Yes
yes	Yes

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]1[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]6[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=COUNTIF($A$2:A2,"YES")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=COUNTIF($C$2:C2,"YES")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your quick reply.
One more question how to set a conditional formatting to highlighted whoever get higher number.For example if supplier 2 has more "yes" I want to highlighted all supplier 2 data

 

[nature969]

Hi Peter

Thank you

if I use your suggestion. how to show trend in colours? e.g more "yes" the greater colour the supplier data should show

 

[RasGhul]

Hi Nature,

Peter's great solution color codes the Best supplier, but we could also re-configure this so that the best supplier is highlighted in that column.

To Color Scale the Yes counts;

Highlight A1:E1 / Conditional Format/Color Scales


To highlight Best Supplier Column;

Highlight A2:E12, Conditional Format/Use a Formula/ =A$1=LARGE($A$1:$E$1,1)

Is this getting close?

Book1
	A	B	C	D	E
1	5	3	2	7	4
2	Supp 1	Supp 2	Supp 3	Supp 4	Supp 5
3	No	No	No	Yes	Yes
4	No	Yes	No	No	No
5	Yes	Yes	No	Yes	No
6	No	No	Yes	Yes	Yes
7	No	No	Yes	No	Yes
8	Yes	No	No	Yes	No
9	No	No	No	No	No
10	Yes	No	No	Yes	No
11	Yes	No	No	Yes	No
12	Yes	Yes	No	Yes	Yes
Sheet1

Cell Formulas
Range	Formula
A1	=COUNTIF(A2:A12,"YES")

 

[nature969]

Thanks again

Hi Nature,

Peter's great solution color codes the Best supplier, but we could also re-configure this so that the best supplier is highlighted in that column.

To Color Scale the Yes counts;

Highlight A1:E1 / Conditional Format/Color Scales


To highlight Best Supplier Column;

Highlight A2:E12, Conditional Format/Use a Formula/ =A$1=LARGE($A$1:$E$1,1)

Is this getting close?

	A	B	C	D	E

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Supp 1[/TD]
[TD="align: center"]Supp 2[/TD]
[TD="align: center"]Supp 3[/TD]
[TD="align: center"]Supp 4[/TD]
[TD="align: center"]Supp 5[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A1[/TH]
[TD="align: left"]=COUNTIF(A2:A12,"YES")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]