Adding a element to formula to make STATUS of "Cancelled" = HEALTH (Green)

KCurry

Here is my current formula that updates the % Complete in my plan. I need to have something in the formula that recognizes the status type of "Cancelled" and marks it 100% Complete like the "Complete" status so that the Health calculates that task/item as green instead of Red. Current when someone marks a task as Cancelled it flags it as Red HEALTH in the sheet and makes their reporting look like there are issues with the plan/project. We want to correct this issue so reporting/HEALTH is correct.

% Complete Formula:

=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(Status@row = "Completed", 1, IF(OR(Status@row = "In Progress", Status@row = "Proofing Approved", Status@row = "Proofing Rejected"), 0.5, IF(Status@row = "On Hold", 0.25, IF(Status@row = "Not Started", 0, "")))))

Health Formula:

=IF([End Date]@row = "", "Green", IF(OR(Level@row = 0, Level@row = 1), IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Blue"))), IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY(7)), "Yellow", "Green")))))

Paul Newcome

Try this...

=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(OR(Status@row = "Cancelled", Status@row = "Completed"), 1, IF(OR(Status@row = "In Progress", Status@row = "Proofing Approved", Status@row = "Proofing Rejected"), 0.5, IF(Status@row = "On Hold", 0.25, IF(Status@row = "Not Started", 0, "")))))

Paul Newcome

Try this...

=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IF(OR(Status@row = "Cancelled", Status@row = "Completed"), 1, IF(OR(Status@row = "In Progress", Status@row = "Proofing Approved", Status@row = "Proofing Rejected"), 0.5, IF(Status@row = "On Hold", 0.25, IF(Status@row = "Not Started", 0, "")))))