Status updating parent row with conditions

allison.worley

I am trying to update the parent row for the following:

If any children = In Progress, Not Started, or blank then In Progress

If all children = Pass then Pass

If any children = fail then Fail

If any children = retest then Retest

If any children = Out of scope or Deferred - Future, then defer to the other rules to determine status (1 is out of scope and all others are pass then pass, 1 is out of scope, 1 is retest and all others are pass then retest)

I have written the below to account for all but the out of scope and deferred. I have tested it and know it works for all statues including returning data for blank fields.

=IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail", IF(COUNTIF(CHILDREN(), "Retest") > 0, "Retest", IF(COUNTIFS(CHILDREN(), OR(@cell = "In Progress", @cell = "Not Started", @cell = "")) > 0, "In Progress", IF(COUNTIF(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN()), "Pass"))))

Can you help me add the formula for If any children = Out of scope or Deferred - Future, then defer to the other rules to determine status (1 is out of scope and all others are pass then pass, 1 is out of scope, 1 is retest and all others are pass then retest). I have been running in circles.

Paul Newcome

All you should need to do is adjust the "Pass" argument to be an OR(@cell = "Pass", @cell = "Out of scope or Deferred - Future").

The rest should work fine already because you are saying to output (for example) "Retest" if there is at least one regardless of the other statuses.

allison.worley

This would work, Thank you. However today I was asked to change where the not started and blank do not follow in Progress unless there is a pass or in progress noted:

I like to take each part apart to understand the formula. Below are my thoughts.

If any children = fail then Fail

If any children = in progress then In Progress

If all children = Pass then Pass

*If all children = Not Started, Blank or a combination of blank and not started = Not started

*If even one in progress or Pass and the rest not started or blank then = In Progress

I cannot find a combination of the last 2 where one does not override the other = either not started or In progress. I have been working on this half the week so really appreciate your help above and any future feedback.

If any children = fail then Fail IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail"

If any children = retest then Retest IF(COUNTIF(CHILDREN(), "Retest") > 0, "Retest", 

If all children = Pass then Pass IF(COUNTIF(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN()), "Pass"

***My challenge is the All not started or blank and the Not Started, blank, Pass = In progress combinations. I have tried the below plus about 100 more combinations. I have tried breaking it down to build it back together but this puzzle is just not fitting

if all blank or not started then Not Started IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "")) > 0, "Not Started" Always returns Not Started

If any children = in progress then In Progress: IF(COUNT(CHILDREN(), "In Progress") "In Progress" Always returns "In Progress"

• IF(COUNTIFS(CHILDREN(), OR(@cell = "In Progress", @cell = "Not Started", @cell = "")) > 0, "In Progress" this always returns in progress even if all are not started or Blank

***Below works for all scenarios except if only Pass and Blank.

=IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail", IF(COUNTIF(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN()), "Pass", IF(COUNTIF(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress")))

Paul Newcome

Try this:

=IF(COUNTIFS(CHILDREN(), @cell = "Fail")> 0, "Fail", IF(COUNTIFS(CHILDREN(), @cell = "Retest")> 0, "Retest", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "")) = COUNT(CHILDREN()), "Not Started", IF(AND(COUNTIFS(CHILDREN(), @cell = "") = 0, COUNTIFS(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN())), "Pass", "In Progress"))))

allison.worley

Adding in the "Out of scope" and "Deferred - Future" options I have made the below work except for the below. any help?

if all are Out of Scope and Deferred combination (This will = pass which I think may be ok)

The other problem as noted earlier is if pass and Blank combination, out of scope and blank combination, or Deferred- Future and blank combination (this should = In progress) but is showing up as the value associated.

=IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail", IF(COUNTIF(CHILDREN(), "Retest") > 0, "Retest", IF(COUNTIF(CHILDREN(), @cell = "Deferred - Future") = COUNT(CHILDREN()), "Deferred - Future", IF(COUNTIF(CHILDREN(), @cell = "Out of Scope") = COUNT(CHILDREN()), "Out of Scope", IF(COUNTIF(CHILDREN(), OR(@cell = "Pass", @cell = "Out of scope", @cell = "Deferred - Future")) = COUNT(CHILDREN()), "Pass", IF(COUNTIF(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))))))

Paul Newcome

You missed the AND portion for the "Pass" argument to include that there are no blanks.

=.............................., IF(AND(COUNTIFS(CHILDREN(), @cell = "") = 0, COUNTIFS(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN())), "Pass", ..........................

allison.worley

Paul, thank you for responding. I am not sure I am putting this in the formula correctly because it is still returning Pass. If there is pass and blank combination it should return "In progress". I have changed this so many directions. Currently my formula solves for all except:

Blank and Out of scope combination = In Progress

Blank and Pass combination = In Progress

Blank and Deferred - future combination = In Progress

=IF(COUNTIF(CHILDREN(), "Fail") > 0, "Fail", IF(COUNTIF(CHILDREN(), "Retest") > 0, "Retest", IF(COUNTIF(CHILDREN(), @cell = "Deferred - Future") = COUNT(CHILDREN()), "Deferred - Future", IF(COUNTIF(CHILDREN(), @cell = "Out of Scope") = COUNT(CHILDREN()), "Out of Scope", IF(COUNTIF(CHILDREN(), OR(@cell = "Pass", @cell = "Out of scope", @cell = "Deferred - Future")) = COUNT(CHILDREN()), "Pass", IF(COUNTIF(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))))))

Where do I add the AND statement...

IF(AND(COUNTIFS(CHILDREN(), @cell = "") = 0, COUNTIFS(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN())), "Pass",

...and how will that make the end result In Progress? I aspire to be as good at formulas, so I want to understand the logic as well.

Paul Newcome

You would drop it into your formula in the same place I have it in my example. In the logical statement portion that outputs "Pass".

=IF(COUNTIFS(CHILDREN(), @cell = "Fail")> 0, "Fail", IF(COUNTIFS(CHILDREN(), @cell = "Retest")> 0, "Retest", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "")) = COUNT(CHILDREN()), "Not Started", IF(AND(COUNTIFS(CHILDREN(), @cell = "") = 0, COUNTIFS(CHILDREN(), @cell = "Pass") = COUNT(CHILDREN())), "Pass", "In Progress"))))