Hello,
I am a beginner Smartsheet user and I have listed my set of requirement to track my schedule health task. The requirements are based off % Complete and % Time Elapsed.
Requirements below:
- Tasks that show status of “Complete” - Blue
- Status in progress, where the % Complete is greater than or equal to Time elapsed. - Green
- Status in Progress, % complete is less than the time elapsed % AND time elapsed % is no more than 20 % greater than the completed %. - Yellow
- Status in Progress & Not Started, % complete is less than the time elapsed % AND time elapsed % is more than 20 % greater than the completed % - RED
- Task that show any one of the fields blank (Optional).
My attempt at creating this formula -
=IF(OR([Start Date]@row = "", [End Date]@row = "", [% Complete]@row = "", [Status]@row = ""), "",
IF([Status]@row = "Complete", "Blue",
IF([Status]@row = "In Progress", [% Complete]@row >= [% Time Elapsed]@row, "Green",
IF([Status]@row = "In Progress", [% Complete]@row < [% Time Elapsed]@row, "Yellow",
IF([Status]@row = "In Progress", [% Time Elapsed]@row, [% Complete] > 0.2, "Yellow",
IF([Status]@row = "Not Started", [% Time Elapsed]@row, [% Complete] < 0.2, "Red",
IF([Status]@row = "In Progress", [% Time Elapsed]@row, [% Complete] < 0.2, "Red")))))
I am getting an unsparesable error. Any help and guidance is greatly appreciated.
Please and Thank you!