Formula

Having difficulty with a formula setting task state (Not Started, In Progress, Complete, On Hold) depending upon how the columns on either side are set - Status/RYGB and % Complete.

=IF(AND(Status@row=“B”, [% Complete]@row =1),"Complete”,IF(AND(Status@row= "G”,[% Complete]@row=0), "Not Started”, IF(AND(Status(G,Y,R)@row= “G”, “Y”, “R”, [% Complete]@row < 0),”In Progress",IF(AND([On Hold]@row = 1), "On Hold”)))

Thanks

Tags:

Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/25/21 Answer ✓

    Hi @Melitta

    Hope you are fine, as i understand that you use for status column BGYR symbols column type if so then try the following formula.

    =IF(AND(Status@row = "Blue", [% Complete]@row = 1), "Complete", IF(AND(Status@row = "Green", [% Complete]@row = 0), "Not Started", IF(AND(OR(Status@row = "Green", Status@row = "Yellow", Status@row = "Red"), [% Complete]@row > 0), "In Progress", "On Hold")))

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Melitta
    Melitta ✭✭✭✭✭
    Answer ✓

    Hello @Bassam.M Khalil

    Thank you that is very helpful, and gets me a closer to the final solution. It also makes me realize that I left out a piece.

    There is another column: On Hold. It's a checkbox. If it's checked, I want the Task State to be "On Hold". Unchecked and it should follow the rest of the formula.

    How do I combine them?

    =IF([On Hold]@row = 1, "On Hold")

    =IF(AND(Status@row = "Blue", [% Complete]@row = 1), "Complete", IF(AND(Status@row = "Green", [% Complete]@row = 0), "Not Started", IF(AND(OR(Status@row = "Green", Status@row = "Yellow", Status@row = "Red"), [% Complete]@row > 0), "In Progress")))

    Thanks,

    Melitta

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/25/21 Answer ✓

    Hi @Melitta

    Hope you are fine, as i understand that you use for status column BGYR symbols column type if so then try the following formula.

    =IF(AND(Status@row = "Blue", [% Complete]@row = 1), "Complete", IF(AND(Status@row = "Green", [% Complete]@row = 0), "Not Started", IF(AND(OR(Status@row = "Green", Status@row = "Yellow", Status@row = "Red"), [% Complete]@row > 0), "In Progress", "On Hold")))

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Melitta
    Melitta ✭✭✭✭✭
    Answer ✓

    Hello @Bassam.M Khalil

    Thank you that is very helpful, and gets me a closer to the final solution. It also makes me realize that I left out a piece.

    There is another column: On Hold. It's a checkbox. If it's checked, I want the Task State to be "On Hold". Unchecked and it should follow the rest of the formula.

    How do I combine them?

    =IF([On Hold]@row = 1, "On Hold")

    =IF(AND(Status@row = "Blue", [% Complete]@row = 1), "Complete", IF(AND(Status@row = "Green", [% Complete]@row = 0), "Not Started", IF(AND(OR(Status@row = "Green", Status@row = "Yellow", Status@row = "Red"), [% Complete]@row > 0), "In Progress")))

    Thanks,

    Melitta

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Melitta

    You are welcome and am happy you find it helpful, Please help the Community by marking it as the accepted answer.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!