Formula to automate status

Options

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?

Answers

  • jaimelynn0814FEB
    jaimelynn0814FEB ✭✭✭✭
    Options

    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
    Mark.poole ✭✭✭✭✭✭
    Options

    @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”))))))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • swfascetti
    swfascetti ✭✭✭✭✭
    Options

    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)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!