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

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
-
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
-
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
-
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
-
Hi Brent
Thank you so much. This works perfectly.
Warmest Regards
Dickson
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!