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

Find more posts tagged with

Accepted answers

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

All comments

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

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?

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.