Help with conditional formula

Hi i am asking your help to replicate the following formula that i have in excel to SmartSheet

=IF(AND(Q2<>P2,R2="No- implementation date is at risk without external (to the project team) involvement"),"Red - Off Track",(IF(AND(Q2<>P2,R2="Yes- implementation date is still achievable"),"Yellow - At Risk",(IF(Q2<=P2,"Green - On Track",(IF(AND(Q2<>P2,OR(R2=0,R2="N/A")),"Red - Off Track","Gray - Not Started")))))))

Formula it is working on column in green. I have the same column in the SmartSheet cause this is excel in the first moment it is a pull from the data in SmartSheet but after that somebody create this column in excel and i need to have it directly in the SmartSheet. That will going to help to automate a Dashboad in PowerBI from SmartSheet. In advance i appreciate.

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    I think you're on the right track here. Make sure your Date columns are actual date-type columns with true date values in them. Add a space after each comma, and spaces before and after operators. You probably only need 4 end-parentheses at the end of your formula. Smartsheet's color-coding of parentheses can help you make sure all interior statements are closed off as well as the exterior statements.

    Replace Q2, P2, and R2 with the names of your columns, and reference @row as follows:

    [Project Plan Start Date]@row

    [Project Actual Start Date]@row

    [Path to Green for Project Start Date?]@row

    =IF(AND(Q2 <> P2, R2 = "No- implementation date is at risk without external (to the project team) involvement"), "Red - Off Track", (IF(AND(Q2 <> P2, R2 = "Yes- implementation date is still achievable"), "Yellow - At Risk", (IF(Q2 <= P2, "Green - On Track", (IF(AND(Q2 <> P2, OR(R2 = 0, R2 = "N/A")), "Red - Off Track", "Gray - Not Started"))))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • @Jeff Reisman i got "UNPARSEABLE" error, would you mind take a look below?

    =IF(AND([Project Plan Start Date]@row<>[Project Actual Start Date]@row, [Path to Green for Project Start Date?]@row = "No-Implementation date is at risk without external (to project team) involvement"), "Red - Off Track", IF(AND([Project Plan Start Date]@row<>[Project Actual Start Date]@row, [Path to Green for Project Start Date?]@row = "Yes- implementation date is still achievable"), "Yellow - At Risk", IF([Project Plan Start Date]@row<=[Project Actual Start Date]@row), "Green - On Track", IF(AND([Project Plan Start Date]@row<>[Project Actual Start Date]@row, OR ([Path to Green for Project Start Date?]@row = 0, [Path to Green for Project Start Date?]@row = "N/A")), "Red - Off Track", "Gray - Not Started")))

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    I think you need a forth end parentheses at the end of the formula, and maybe spaces on either side of each instance of "<>" or "<=". You may be running into trouble with your "OR" inside and embedded "AND"; I've never tried that before. There's also an extraneous ")"before "Green - On Track":

    <= [Project Actual Start Date]@row), "Green - On Track",

    See changes in BOLD below. If you can share a screenshot of your formula from within Smartsheet, the color-coding Smartsheet uses in formulas can help troubleshoot.

    =IF(AND([Project Plan Start Date]@row <> [Project Actual Start Date]@row, [Path to Green for Project Start Date?]@row = "No-Implementation date is at risk without external (to project team) involvement"), "Red - Off Track", IF(AND([Project Plan Start Date]@row <> [Project Actual Start Date]@row, [Path to Green for Project Start Date?]@row = "Yes- implementation date is still achievable"), "Yellow - At Risk", IF([Project Plan Start Date]@row <= [Project Actual Start Date]@row, "Green - On Track", IF(AND([Project Plan Start Date]@row <> [Project Actual Start Date]@row, OR ([Path to Green for Project Start Date?]@row = 0, [Path to Green for Project Start Date?]@row = "N/A")), "Red - Off Track", "Gray - Not Started"))))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!