Conditional Formating

New Contributor


I want to make new column based on the data in picture. There are 3 columns which are R, Y and B. If 2 of the 3 columns Yellow, or 1 of the 3 columns Red will label as "Priority 1". If 1 of the column Yellow but rest are green, will label as "No Action"... Other than that will "Priority 2". Can you guys guide me on this. Thank you.

3 Replies
ok so you label this as conditional formatting but you want things to get "labelled". Where is this label going to show? In the next column to the right of 'B'? And you want to base this label on the color of the cells? How are those cells getting those colors? conditional formatting?
Based on what I see and read I am GUESSING that there is conditional formatting on the columns R, Y and B based on the categories above. That said, you want "Priority 1" for 2 of 3 columns "Yellow" or 1 of 3 "Red" but don't mention orange. So this is a bit confusing.
That all said if you use a formula like
=CHOOSE( MAX(1,MIN((A7>50)+(A7>500)+(B7>50)+(B7>500)+(C7>50)+(C7>500)),3), "No Action", "Priority 1", "Priority 2")
Basically what I'm suggesting is to compare each cell to see if it is at least yellow (which would result in a value of 1) and if that same cell is red (which would be another 1) than add them all together. So all < then yellow would result in 0, if exactly 1 is yellow and 2 green then =1, and if either 2 are yellow or 1 is red then =2 and if more than 2 yellow or more than 1 red then it is >2.
Then I used MIN to make the min 1 so either 0 or 1 will still be 1 and MAX to make the max 3 and then the CHOOSE will select the 1st, 2nd, or 3rd text accordingly.
I hope I interpreted your need correctly.

Yes. The new column will be next to column B. So, if 2 of the 3 columns (R,Y,B) is above 50, then at new column will display "Priority 1". Also, if one of the 3 columns (R,Y,B) above 500, the new column will also display "Priority 1". Then, if 3 columns (R,Y,B) less than 50 or only one of the column is greater than 50 but lower than 150 and the rest green, it will display "No Action". Other than what I have stated before, will display "Priority 2".@mtarler 

best response confirmed by Firdaus1998 (New Contributor)
Yep, that is what the formula will do.
depending on your version of Excel, you can also use dynamic arrays and make it a little cleaner
=CHOOSE( MAX(1,MIN(SUM((A7:C7>50),(A7:C7>500)),3), "No Action", "Priority 1", "Priority 2")