RYG formula based on checkboxes

Monique Prizgar

@Paul Newcome

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?

Paul Newcome

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")))

Paul Newcome

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")))

Monique Prizgar

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

Paul Newcome

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")))

Monique Prizgar

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.

Paul Newcome

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")))

Monique Prizgar

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.

Paul Newcome

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.

Monique Prizgar

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")))

Monique Prizgar

I appreciate all your help so far, it's ALMOST working!

Paul Newcome

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")))

Monique Prizgar

That's it! Perfect, and thank you so much!!!

Paul Newcome

Happy to help. 👍️