IF AND/OR Nesting

Options

Hi there!

I would like to combine all of these values into one "Health" column (four colors). See chart attached. How would they be combined?


Answers

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

    To nest multiple AND functions inside of an OR function (grouping the "Blue" outputs for example) you treat the entire AND as if it is a single "logical statement" within the OR function.


    =IF(OR(AND(..................), AND(...................)), "Blue")

  • Carrie Tortorella
    Options

    Thank you @Paul Newcome I appreciate you making it simple for me. I was overthinking this. Would this work? I get an unparseable error.

    =IF(OR(AND([Account has Updated Renewal Added to TL]@row = 0, [Round #3 Price Sheet Status]@row = “Not Renewing”),AND([Account has Updated Renewal Added to TL]@row = 1, [Round #3 Price Sheet Status]@row = “Billing Approved”), "Blue”, IF([Price Sheet Deadline]@row >= TODAY(20), "Green", IF([Price Sheet Deadline]@row < TODAY(18), "Red", IF([Price Sheet Deadline]@row < TODAY(20), "Yellow")))

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

    It looks like there are a couple of issues.


    You are missing a closing parenthesis to finish off the OR function. There are also "smart quotes" in your formula. See how some of your quotes are slanted? Those are call "Smart Quotes" which (ironically enough) are not recognized as valid characters by Smartsheet.


    See how some of your quotes as well as all of the quotes in this comment are straight up and down? Those are the ones you want. You can get them by typing directly in Smartsheet, here in the Community, or in a text editor such as Notepad (not Word).


    Below is the formula with the closing parenthesis and quotes fixed. If this does not work then we can start diving in a little deeper.


    =IF(OR(AND([Account has Updated Renewal Added to TL]@row = 0, [Round #3 Price Sheet Status]@row = "Not Renewing"),AND([Account has Updated Renewal Added to TL]@row = 1, [Round #3 Price Sheet Status]@row = "Billing Approved")), "Blue", IF([Price Sheet Deadline]@row >= TODAY(20), "Green", IF([Price Sheet Deadline]@row < TODAY(18), "Red", IF([Price Sheet Deadline]@row < TODAY(20), "Yellow")))

  • Carrie Tortorella
    Options

    That works! Thank you for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!