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.
Answers
-
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")))
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!