RYG formula based on checkboxes
I know you can help me. :) I got this far from your previous posts, but I'm going cross eyed now.
=IF(AND([Trusted Advisor/Last Look]1 = 1, ABS1 = 1, Service1 = 1), "Blue", IF(OR([Trusted Advisor/Last Look]1 = 1, ABS1 = 1, Service1 = 1), "Green", IF(OR(Service1 = 1), "Yellow", IF(AND(ABS1 = 1), "Yellow", "Red"))))
I want blue if all columns are checked (working)
I want Red if no columns are checked (Working)
I would like green if abs and service are checked, but trusted advisor is not
I would like green if service and trusted advisor is checked but abs is not
I would like yellow if either abs or service are checked and trusted advisor is not
Can you help me?
Best Answer
-
Right. I understood the TA/LL. I needed clarification on what color you wanted for those two possibilities. To get those two to be green, this would be the adjustment:
=IF(AND([Trusted Advisor/Last Look]1 = 1, ABS1 = 1, Service1 = 1), "Blue", IF(OR(AND([Trusted Advisor/Last Look]1 <> 1, ABS1 = 1, Service1 = 1), AND([Trusted Advisor/Last Look]1 = 1, OR(ABS1 = 1, Service1 = 1))), "Green", IF(OR(AND([Trusted Advisor/Last Look]1 <> 1, OR(ABS1 = 1, Service1 = 1)), AND([Trusted Advisor/Last Look]1 = 1, ABS1 <> 1, Service1 <> 1)), "Yellow", "Red")))
Answers
-
Try something like this:
=IF(AND([Trusted Advisor/Last Look]1 = 1, ABS1 = 1, Service1 = 1), "Blue",Β IF(AND([Trusted Advisor/Last Look]1 <> 1, ABS1 = 1, Service1 = 1), "Green",Β IF(AND([Trusted Advisor/Last Look]1 = 1, OR(ABS1 = 1, Service1 = 1)), "Yellow",Β "Red")))
-
If I check service and abs, it's green, good
if I check all 3, blue, good
but no yellow if I check service OR abs alone, stays red
-
Sorry about that. Forgot to update a symbol. Try this:
=IF(AND([Trusted Advisor/Last Look]1 = 1, ABS1 = 1, Service1 = 1), "Blue",Β IF(AND([Trusted Advisor/Last Look]1 <> 1, ABS1 = 1, Service1 = 1), "Green",Β IF(AND([Trusted Advisor/Last Look]1 <> 1, OR(ABS1 = 1, Service1 = 1)), "Yellow",Β "Red")))
-
THAT's the ticket! So another question, if I wanted to get yellow for trusted advisor only being checked?
Thank you so much, I wasn't getting here without help.
-
The tweak for Yellow when only Trusted is checked would be:
=IF(AND([Trusted Advisor/Last Look]1 = 1, ABS1 = 1, Service1 = 1), "Blue",Β IF(AND([Trusted Advisor/Last Look]1 <> 1, ABS1 = 1, Service1 = 1), "Green",Β IF(OR(AND([Trusted Advisor/Last Look]1Β <>Β 1, OR(ABS1 = 1, Service1 = 1)), AND([Trusted Advisor/Last Look]1 = 1, ABS1 <> 1, Service1 <> 1)), "Yellow",Β "Red")))
-
well that gives me the yellow when only TA/LL is checked, but if I check ABS with TA/LL it's red and if I check service and TA/LL it's red
I need green for those.
-
Right. I didn't see anywhere that you specified you needed that (TA/LL = 1 & service or ABS = 1). What would be the expected output for that?
So far all I see specified are the colors in the below table. Unhighlighted rows haven't been specified and will default to red (include none checked).
Anything unhighlighted above that you would want a different color than Red would also need to be specified in the formula.
-
That was me abbreviating Trusted Advisor/Last Look column
What we're trying to do is come up with customer tiers
This sheet has our customers listed and what they've spent with our construction division
They can look to us as a trusted advisor or give us last look at proposals before they award, so I want them to be yellow if that's the only box checked. That means it's a strong customer, but only for one division.
ABS and Service are our other departments, so the more integration they have with our company, the higher the rating goes - hence blue when all boxes are checked, tier 1.
If they also do business either ABS OR service, they are yellow, if it's both, they are green, and if it's both ABS and service and trusted advisor status as well, that's when the blue comes in.
Red is when it's only construction, and no boxes are checked.
Everything else is working, but if they are trusted advisor/last look and only ONE of the other divisions, it stays red and I need green there
=IF(AND([Trusted Advisor/Last Look]1 = 1, ABS1 = 1, Service1 = 1), "Blue", IF(AND([Trusted Advisor/Last Look]1 <> 1, ABS1 = 1, Service1 = 1), "Green", IF(OR(AND([Trusted Advisor/Last Look]1 <> 1, OR(ABS1 = 1, Service1 = 1)), AND([Trusted Advisor/Last Look]1 = 1, ABS1 <> 1, Service1 <> 1)), "Yellow", "Red")))
-
I appreciate all your help so far, it's ALMOST working!
-
Right. I understood the TA/LL. I needed clarification on what color you wanted for those two possibilities. To get those two to be green, this would be the adjustment:
=IF(AND([Trusted Advisor/Last Look]1 = 1, ABS1 = 1, Service1 = 1), "Blue", IF(OR(AND([Trusted Advisor/Last Look]1 <> 1, ABS1 = 1, Service1 = 1), AND([Trusted Advisor/Last Look]1 = 1, OR(ABS1 = 1, Service1 = 1))), "Green", IF(OR(AND([Trusted Advisor/Last Look]1 <> 1, OR(ABS1 = 1, Service1 = 1)), AND([Trusted Advisor/Last Look]1 = 1, ABS1 <> 1, Service1 <> 1)), "Yellow", "Red")))
-
That's it! Perfect, and thank you so much!!!
Help Article Resources
Categories
Check out the Formula Handbook template!