RYG formula based on checkboxes

Options

@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?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Monique Prizgar ✭✭✭
    edited 01/18/23
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!