decision formula/ conditional formatting

nature969

nature969

Board Regular
Joined
Dec 20, 2016
Messages
55
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]


 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
You could use helper columns to count "yes", and then Conditional Format the counting columns with Data Bars?


Book1
ABCD
1supplier 1supplier 2
2yes1NO0
3No1Yes1
4Yes2Yes2
5yes3Yes3
6No3Yes4
7Yes4Yes5
8yes5Yes6
Sheet1
Cell Formulas
RangeFormula
B2=COUNTIF($A$2:A2,"YES")
D2=COUNTIF($C$2:C2,"YES")
 
Upvote 0
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
ABCDE
153256
2Supp 1Supp 2Supp 3Supp 4Supp 5
3NoNoNoYesYes
4NoYesNoNoNo
5YesYesNoNoYes
6NoNoYesNoYes
7NoNoYesNoYes
8YesNoNoYesYes
9NoNoNoNoNo
10YesNoNoYesNo
11YesNoNoYesNo
12YesYesNoYesYes
Best Suppliers
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A21. / Formula is =A$1=LARGE($A$1:$E$1,1)Abc
A22. / Formula is =A$1=LARGE($A$1:$E$1,2)Abc
A23. / Formula is =A$1=LARGE($A$1:$E$1,3)Abc
A24. / Formula is =A$1=LARGE($A$1:$E$1,4)Abc




Here is my sheet again with some different data.

Excel Workbook
ABCDE
153274
2Supp 1Supp 2Supp 3Supp 4Supp 5
3NoNoNoYesYes
4NoYesNoNoNo
5YesYesNoYesNo
6NoNoYesYesYes
7NoNoYesNoYes
8YesNoNoYesNo
9NoNoNoNoNo
10YesNoNoYesNo
11YesNoNoYesNo
12YesYesNoYesYes
Best Suppliers
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A21. / Formula is =A$1=LARGE($A$1:$E$1,1)Abc
A22. / Formula is =A$1=LARGE($A$1:$E$1,2)Abc
A23. / Formula is =A$1=LARGE($A$1:$E$1,3)Abc
A24. / Formula is =A$1=LARGE($A$1:$E$1,4)Abc
 
Last edited:
Upvote 0
RasGhul said:
You could use helper columns to count "yes", and then Conditional Format the counting columns with Data Bars?

ABCD
supplier 1supplier 2
yesNO
NoYes
YesYes
yesYes
NoYes
YesYes
yesYes

<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]
Click to expand...

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
 
Upvote 0
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
 
Upvote 0
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.

TWAJD8u.jpg


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
ABCDE
153274
2Supp 1Supp 2Supp 3Supp 4Supp 5
3NoNoNoYesYes
4NoYesNoNoNo
5YesYesNoYesNo
6NoNoYesYesYes
7NoNoYesNoYes
8YesNoNoYesNo
9NoNoNoNoNo
10YesNoNoYesNo
11YesNoNoYesNo
12YesYesNoYesYes
Sheet1
Cell Formulas
RangeFormula
A1=COUNTIF(A2:A12,"YES")
 
Upvote 0
Thanks again

RasGhul said:
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.

TWAJD8u.jpg


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?

ABCDE

<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]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
993
AlanY
A
G
  • Question Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
1K
Godders199
G
A
  • Question Question
Add Sequence numbers Based on Dates
Replies
1
Views
3K
Jeffrey Mahoney
Jeffrey Mahoney
T
  • Question Question
VBA to launch userform by selecting a cell
2
Replies
10
Views
2K
Logit
Logit
H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top