Formula to automate status

Joechaptaylor

Hi,

I would like to automate my Status column, based on % Complete.

If My % Complete is 0, the status will = Not Started

If my % Complete if between 1 and 100% the status will = In Progress

If my % Collum is 100 the status will = Complete

Also, if column "Hold" is checked, status will = Hold

Also, If column "Review" is checked, status will = Review

Also, if column "Sign Off" is checked, status will = Sign Off.

Any ideas, or are there better ways to be able to reprisent the last 3 statuses at all?

jaimelynn0814FEB

Would this work?

=IF([Hold]@row = 1, "Hold",
IF([Review]@row = 1, "Review",
IF([Sign Off]@row = 1, "Sign Off",
IF([% Complete]@row = 0, "Not Started",
IF([% Complete]@row = 100, "Complete",
"In Progress")))))

Explanation:

1. Hold: First, it checks if the "Hold" checkbox is checked. If yes, it sets the status to "Hold".
2. Review: Next, it checks the "Review" checkbox, setting the status to "Review" if checked.
3. Sign Off: Then, it checks if the "Sign Off" checkbox is checked, setting the status to "Sign Off" if true.
4. % Complete Conditions:
   • Checks if "% Complete" is 0, setting the status to "Not Started".
   • Checks if "% Complete" is 100, setting the status to "Complete".
   • If none of the above conditions are met and "% Complete" is between 1 and 99, the status is set to "In Progress".

Mark.poole

@Joechaptaylor

=IF(Hold@row = 1,”Hold”,IF(Review@row =1,”Review”,IF[Sign Off]@row=1,”Sign Off”,IF([% Complete]@row =0, “Not Started”,IF([% Complete]@row <100, “In Progress”,IF([% Complete]@row =100, “Complete”))))))

swfascetti

I assume that the checked boxes take precedence over the %status. How about building it with two columns. The Column "Status %" is a nested IF Statement to assign status based on % Complete. The Second column is another nested IF Statement that gives precedence in the following order: Sign Off / Review / Hold / % Status. See functions below:

"Status %": =IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 100, "Complete", "In Progress"))

"Status": =IF([Sign Off]@row = true, "Sign Off", IF(Review@row = true, "Review", IF(Hold@row = true, "Hold", [Status %]@row)))