Multi conditional formula affecting the "Status" on a Project Plan

Options
DicksonK
DicksonK ✭✭✭
edited 07/22/22 in Formulas and Functions

Hi There

Can one have a formula in a project plan that says if the "Activity " column is "Project Task" and the "% Complete" 10%, then Status is "In Progress". However, if the "Activity" is changed to "No Longer Applicable", or "Hold" - the "% Complete" remains unchanged, but the Status is then either "No Longer Applicable", or "Hold"?

In the Status column, there is a formula currently that says that if the % Complete= 0% then the Status is "Not Started", between 1% and 99% the Status = In Progress, and, if it is 100% then the Status = "Complete"

Thank you in advance

Dickson

Best Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓
    Options

    This should work

    =IF([Activity]@row="Project Task", IF([% Complete]@row=0, "Not Started", IF([% Complete]@row=1, "Complete", "In Progress")), IF([Activity]@row="Hold", "Hold", "No Longer Applicable"))

    I am using the decimal numbers (1=100%, 0.5 would equal 50%)

    My logic first checks for Project Task if that it true then the True case checks for %Complete = 0% and if true then "Not Started" if it is not Zero then I check for 100% and if So then it is "Complete" everything else is "In Progress"

    If the Project Task was false then the false logic simply figures out which one it is.

    IF([Activity]@row="Hold", "Hold", "No Longer Applicable")

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓
    Options

    @DicksonK

    Does this work?

    =IF( [Activity]@row="Project Task", IF( [% Complete]@row=0, "Not Started", IF( [% Complete]@row=1, "Complete", "In Progress")), IF([Activity]@row="Hold", "Hold", IF( [Activity]@row="Cancelled", "Cancelled", "No Longer Applicable")))

    When it is Cancelled do you want it to Say Cancelled? If not change the text "Text to Put if Cancelled" below

    =IF( [Activity]@row="Project Task", IF( [% Complete]@row=0, "Not Started", IF( [% Complete]@row=1, "Complete", "In Progress")), IF([Activity]@row="Hold", "Hold", IF( [Activity]@row="Cancelled", "Text to Put if Cancelled", "No Longer Applicable")))

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓
    Options

    This should work

    =IF([Activity]@row="Project Task", IF([% Complete]@row=0, "Not Started", IF([% Complete]@row=1, "Complete", "In Progress")), IF([Activity]@row="Hold", "Hold", "No Longer Applicable"))

    I am using the decimal numbers (1=100%, 0.5 would equal 50%)

    My logic first checks for Project Task if that it true then the True case checks for %Complete = 0% and if true then "Not Started" if it is not Zero then I check for 100% and if So then it is "Complete" everything else is "In Progress"

    If the Project Task was false then the false logic simply figures out which one it is.

    IF([Activity]@row="Hold", "Hold", "No Longer Applicable")

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • DicksonK
    DicksonK ✭✭✭
    Options

    Hi Brent

    Thank you so much. This works perfectly.


    Warmest Regards

    Dickson

  • DicksonK
    DicksonK ✭✭✭
    Options

    Hi Brent

    Apologies, I forgot one of the other "Activity" options is "Cancelled" and I tried adding it to the end off by adding "Cancelled" into the last part, but I get an "Incorrect Argument Set"

    =IF(Activity@row = "Project Task", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", "In Progress")), IF(Activity@row = "On Hold", "On Hold","No Longer Applicable", "Cancelled"))

    How can I fix this?


    Thanks

    Dickson

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓
    Options

    @DicksonK

    Does this work?

    =IF( [Activity]@row="Project Task", IF( [% Complete]@row=0, "Not Started", IF( [% Complete]@row=1, "Complete", "In Progress")), IF([Activity]@row="Hold", "Hold", IF( [Activity]@row="Cancelled", "Cancelled", "No Longer Applicable")))

    When it is Cancelled do you want it to Say Cancelled? If not change the text "Text to Put if Cancelled" below

    =IF( [Activity]@row="Project Task", IF( [% Complete]@row=0, "Not Started", IF( [% Complete]@row=1, "Complete", "In Progress")), IF([Activity]@row="Hold", "Hold", IF( [Activity]@row="Cancelled", "Text to Put if Cancelled", "No Longer Applicable")))

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • DicksonK
    DicksonK ✭✭✭
    Options

    Hi Brent


    Thank you so much, the solution works. I really appreciate your time and effort. I am clueless with formulae and spent hours trying to do this, but it makes so much sense now that I see your solution.


    Warmest Regards

    Dickson

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!