IF/NOT/OR Formula

Options

Hello,

I'm having trouble with my current formula, specifically the =IF(AND([ARU1 in Auto]@row = "Auto", AND([Cold Room Temperature]@row < 7, (Cold Room Temperature)@row 3, "Yellow"

I'm trying to create a yellow symbol if EITHER ARU1 or ARU2 are not in Auto but the temperature specs are within the range.

Any help would be greatly appreciated!

Best Answer

Answers

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭
    Options

    Hi

    Try this:

    =IF(AND(OR([ARU1 in Auto]@row <> "Auto", [ARU2 in Auto]@row <> "Auto"), [Cold Room Temperature]@row < 7), "Yellow", "Green")

    Also, once you have that working the way you want for all conditions, you may wish to change your col. type to be symbol so you get the RYG lights.

    I hope that helps.

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Bryan Letourneau
    Options

    Thanks Kevin, that takes care of the Green/Yellow but incorporating the Red symbol formula into that is now where I'm having trouble.


  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭
    Options

    That is great, getting closer..

    In the case of more conditions, you need to 'nest' your if statements. So where it says "Green", you want to insert your if statement for the next condition, and continue until all your conditions are accounted for. Typically the last IF has something like "ERROR" or would end in "Red" depending on how you want it.

    So

    IF (condition, if true do this, if not true - then another IF(condition, if true do this, if not true then another IF(condition, if true do this, if not true - then another IF(condition, if true, do this, if not "final result")))).

    Something like that. There are lots of nested IF examples but I do find it best to construct each one separate and then insert them as you go. I often right them outside of Smartsheet because working in the little space is hard for me if I have a good number of nested IFs.

    If this info doesn't help, let me know and I can fix the formula with the "Red" condition OR if the "Red" is the only other one, I can fix and send back.

    Let me know. Thanks

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Bryan Letourneau
    Answer ✓
    Options

    That does make sense, I tend to have issues moving into the more complex formulas with multiple conditions but your advice of "nesting" worked well!

    Thanks again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!