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

DicksonK

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

Brent Wilson

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 Wilson

@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 Wilson

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

DicksonK

Hi Brent

Thank you so much. This works perfectly.

Warmest Regards

Dickson

DicksonK

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

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

DicksonK

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