Multiple IF/AND for duration and status
Hi Community,
I am trying to structure an IF/AND formula to reflect the following conditions:
IF [Duration]@row <> 0, AND IF [Complete]@row =0, "Not Started"
IF [Duration]@row <> 0, AND IF [Complete]@row =0, AND IF[Complete]@ previous row = 1, "In Progress"
IF [Duration]@row <> 0, AND IF [Complete]@row =1, "Complete"
IF [Duration]@row = 0, "No Task"
Could somebody kindly help? Hope the above makes sense.
Thank you!
Best Answers

Once an IF condition is met, the remainder are not evaluated, so some of the above complexity can be reduced with sensible ordering of arguments.
As far as I can tell, Smartsheet doesn't offer relative cell references (some additional discussion on row number tracking is available here: https://community.smartsheet.com/discussion/6757/formulatodeterminerownumber).
Consequently, you will need to replace the "X" below with the actual row number you want to reference; it should still update fine when the formula is copied. Another option you may wish to look into is the PARENT() formula, which will apply if you have a parentchild/tasksubtask relationships defined in your dataset.
IF([Duration]@row = 0, "No Task",
IF( [Complete]@row =1, "Complete",
IF([Complete]X =1, "In Progress",
"Not Started" )))

Thank you Joanna, I ended up using:
=IF([Actual days]@row = 0, "No Task", IF(Complete@row = 1, "Complete", IF(Current@row = "Current", "In Progress", "Not Started")))
Where Current is (for row20, in this case):
=IF(AND(Complete19 = 1, Complete20 = 0), "Current", "Not current")
Best
Answers

Once an IF condition is met, the remainder are not evaluated, so some of the above complexity can be reduced with sensible ordering of arguments.
As far as I can tell, Smartsheet doesn't offer relative cell references (some additional discussion on row number tracking is available here: https://community.smartsheet.com/discussion/6757/formulatodeterminerownumber).
Consequently, you will need to replace the "X" below with the actual row number you want to reference; it should still update fine when the formula is copied. Another option you may wish to look into is the PARENT() formula, which will apply if you have a parentchild/tasksubtask relationships defined in your dataset.
IF([Duration]@row = 0, "No Task",
IF( [Complete]@row =1, "Complete",
IF([Complete]X =1, "In Progress",
"Not Started" )))

Thank you Joanna, I ended up using:
=IF([Actual days]@row = 0, "No Task", IF(Complete@row = 1, "Complete", IF(Current@row = "Current", "In Progress", "Not Started")))
Where Current is (for row20, in this case):
=IF(AND(Complete19 = 1, Complete20 = 0), "Current", "Not current")
Best
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!