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
Check out the Formula Handbook template!