Multiple IF / AND / OR Statements Help, Please.

07/30/18 Edited 12/09/19

This is such a great community and a wealth of knowledge for a new Smartsheet user like me. I have done several formulas, but this one has me scratching my head. 

I have a [Target Date] column, [% Complete] column, and status column that I would like to automate with Red, Yellow, Green, and Gray status balls. The [% Complete] column is a drop-down with options: N/A, 0%, 25%, 50%, 75%, and 100%. 

I would like to have no status ball for "N/A". That seems to be the easy part.

Next, I would like the status ball to be Gray if the [% Complete] is 0% and the target date is 5+ days out and Green if the [% Complete] is 25%, 50%, or 75% and 5+ days out. I would like the status balls to change to Yellow if the [% Complete] is 0%, 25%, 50%, or 75% and 3 days or less from [Target Date], and have status balls change to Red if the [% Complete] is 0%, 25%, 50%, or 75% and the [Target Date] is the date before the target date, date of target date, or is past the target date. 

I won't need a status ball for [% Complete] = 100%, as I plan to set conditional formatting to show a completed task. 

I've tried several different variations today and am getting two different formula error messages. I thought I'd post this question, as there are several of you experienced users who seem to excel at these formulas. 

Thank you in advance for your assistance! 

Comments

  • Mike WildayMike Wilday ✭✭✭✭✭

    Any chance you could show us what you created so we can walk you through what to shift to make your formulas work? 

    You are going to have to nest several if statements with And statements to make this happen. 

     

  • Thank you very much for your reply and offer to assist me in this undertaking. I have attached a small sample of a very large task list. The status balls are currently set to be gray if N/A, red if 0% complete, green if in progress or 100%. The risk flags are on/off for tasks that are not 100% complete and past due. In rethinking the status balls, we'd really prefer to have them as I've indicated above and use the conditional formatting and filters to de-clutter the N/A and 100% complete tasks. Thanks again for offering to assist; it is appreciated. 

    Smartsheet sample.JPG

  • Javed HassanJaved Hassan ✭✭✭✭✭

    Plug this into the first row and copy the formula down and it should work. I added a part at the beginning that says if the Target date is blank, then the symbol should be blank. It's the first IF formula, so just replace that second "" with a color if needed.

    =IF([Target Date]1 = "", "", (IF([% Complete]1 = 1, "", IF(AND([% Complete]1 = 0, [Target Date]1 >= TODAY() + 5), "Gray", IF(AND([% Complete]1 < 1, [Target Date]1 >= TODAY() + 5), "Green", IF(AND([% Complete]1 < 1, [Target Date]1 <= TODAY()), "Red", "Yellow"))))))

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    A few comments on the solution from the Peanut Gallery:

    As new users, try to get into the habit of using @row instead of the current row number for references to cells on the same row. It will save you a few headaches in the future when copying things around. It will also aid the transition to a column default formula if Smartsheet ever releases that feature.

    Second, TODAY()+5 and TODAY(5) are functionally equivalent. I find the second easier to read and less likely to have errors.

    I also removed one unnecessary () pair.

    The new formula would look like this:

    =IF([Target Date]@row = "", "",

     IF([% Complete]@row = 1, "",

     IF(AND([% Complete]@row = 0,[Target Date]@row >= TODAY(5)), "Gray",

     IF(AND([% Complete]@row < 1, [Target Date]@row >= TODAY(5)), "Green",

     IF(AND([% Complete]@row < 1, [Target Date]@row <= TODAY()), "Red",

     "Yellow")))))

    Personally, I have a few places where I turn off conditional formatting temporarily, so I tend to avoid blanks when the cell value has meaning, but not importance in most cases. I point that out primarily for anyone looking here for the most recent answer and assuming it will fix their own problem. I would need to further modify it for my own solution because there is a still a piece missing (the formatting).

    Lastly, I prefer to avoid duplication of criteria when using Nested IF's, so I would rewrite the formula as so:

    =IF([Target Date]@row = "", "", IF([% Complete]@row = 1, "", IF([Target Date]@row >= TODAY(5), IF([% Complete]@row = 0, "Gray", "Green"), IF(AND([% Complete]@row < 1, [Target Date]@row <= TODAY()), "Red", "Yellow"))))

    This results in a formula that is shorter (less processing needed) and easier to maintain, in my opinion.

    =IF([Target Date]@row = "", "",

     IF([% Complete]@row = 1, "",

     IF([Target Date]@row >= TODAY(5), 

       IF([% Complete]@row = 0, "Gray", "Green"),

     IF(AND([% Complete]@row < 1, [Target Date]@row <= TODAY()), "Red",

     "Yellow"))))

    Craig

  • Thank you! I really appreciate the help! 

  • Craig - 

    Thank you very much for the help with the formula and additional helpful advice. I appreciate you taking the time and your willingness to help those of us just getting started with Smartsheet. I appreciate and enjoy learning, and you have taught me something. 

    Thanks again, Amy 

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    Then my work here is done Amy.

    Craig

Sign In or Register to comment.