If Formula for Complete, In Progress, At Risk, NA

abbey123

Hi,

I need help with a formula so that the Child can filter to complete, in progress or at risk based on the status for the items of the descendants. Additionally, when NA is set as a status for a line item I don't want NA to be set as the status for the child, since that is not an accurate representation. I think I have the formula started below I just don't know how to add on the 2 Not Applicable's statements I need.

=IF(COUNTIFS(CHILDREN(), "at risk") = COUNT(CHILDREN()), "at risk", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "At risk"))

Nick Korna

Of course - switch round the middle part of the formula slightly:

=IF(COUNTIF(CHILDREN(), "Complete") = (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), "Not Applicable")), "Complete", IF(COUNTIF(CHILDREN(), "At Risk") >= 1, "At Risk", "In Progress"))

Now if any of the child tasks are "At Risk", then the parent will display this.

You can also use the above formula as the basis for your ancestor rows - it will look at the level below and apply the same logic (i.e. complete if all complete, at risk if any are, otherwise in progress).

Nick Korna

Hi @abbey123,

Your formula seems to be suggesting that the parent will be:

"Complete" if all children are either complete or NA

"At Risk" if all children are either complete or NA

Otherwise: In Progress

If these assumptions are correct, then this formula should work:

=IF(COUNTIF(CHILDREN(), "Complete") = (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), "Not Applicable")), "Complete", IF(COUNTIF(CHILDREN(), "At Risk") = (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), "Not Applicable")), "At Risk", "In Progress"))

Example with the formula in the parent Status cells:

If I've misunderstood something or you want some additional refining done, just post the details.

If not, hope this post has helped! 😊

abbey123

Thank you Nick, this definitely did help. The Parent is the "CSU", The Children is the "CSU Disconnect" and the Descendants is "is there a csu to be moved". This formula worked for the most part part however, If a task is marked as at risk the the formula shows up as in progress and not as at risk, is there a way to add at risk as a status?

Nick Korna

Of course - switch round the middle part of the formula slightly:

=IF(COUNTIF(CHILDREN(), "Complete") = (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), "Not Applicable")), "Complete", IF(COUNTIF(CHILDREN(), "At Risk") >= 1, "At Risk", "In Progress"))

Now if any of the child tasks are "At Risk", then the parent will display this.

You can also use the above formula as the basis for your ancestor rows - it will look at the level below and apply the same logic (i.e. complete if all complete, at risk if any are, otherwise in progress).