PARENT CHILDREN FORMULA
Greetings,
I need to formulate an equation in a parent cell.
The children cells indicate certain conditions for each task(row). These conditions are as follows: component received, awaiting component, not required.
I need a function such that, if all children cell indicate "component received", then the parent cell must automatically indicate the same.
Even if one child cell indicates otherwise, the parent cell must indicate "awaiting component"
I tried to use the following: =IF(([PRODUCT STATUS]x:[PRODUCT STATUS]y) = "RECEIVED COMPONENT", "RECEIVED COMPONENT", "AWAITING COMPONENT").
But it doesn't work.
Can anyone suggest changes to the problem?
Thank you
Comments
-
Give this a go...
=IF(COUNTIFS(CHILDREN()), "Awaiting Component") > 0, "Awaiting Component", IF(COUNTIFS(CHILDREN()), OR(@cell = "Received Component", @cell = "Not Required")) = COUNT(CHILDREN()), "Received Component"))
This says that if Awaiting is found anywhere, then it will display Awaiting. If all children are either received or not required, then it will show received.
-
Thank you
-
-
Hi Paul
I have a similar issue, where I have more than one level.
I have Phase, Milestone, Task and sometimes sub-tasks.
I'm trying to achieve a health formula to show:
if there's any Task or subtask in Red then automatically the milestone should change to Yellow
If any milestone is yellow the phase should show as yellow if there's any milestone red then the phase should.
That way I can see that if on that phase anything is either yellow or red then it needs my attention.
Would you use the same logic? and what's your recommended formula for it?
Thanks in advance.
-
Are you able to spell out in detail every scenario you want covered and it's outcome?
-
Hi Paul, sure...
if there's any Task or subtask in Red then automatically the Milestone should change to Yellow
If any Milestone is yellow the Phase should show as Yellow
if there's any Milestone Red then the phase should show as Red.
That way I can see that if on that phase anything is either yellow or red then it needs my attention.
I'm trying to get a health check:
Green - Completed
Blue - In Progress
Yellow - Attention
Red - Delayed
At the moment this is the formula I'm using but it ONLY works on 1 parent up (Task red then Milestone becomes Red but NOT the Phase)
Schedule Hierarchy is:
Phase → Milestone → Task → Subtask
=IF([Start Date]@row= "", "", IF([Start Date]@row > TODAY(), "Blue", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green"))))
Example1:
IF I have 3 layers (Phase → Milestone → Task)
Phase 1 (Green) ← I would like this to show Yellow as at least 1 milestone is yellow
Milestone 1 (Yellow)
Task 1 (Green)
Task 2 (Green)Task 3 (Red)
Milestone 2 (Green)
Task 1 (Green)
Task 2 (Green)Task 3 (Green)
I'm happy to hear your suggestion in how Health should be calculated and the rationale in case I'm looking into it the wrong way.
Thanks
-
Try something like this...
=IF(CONTAINS("Red", CHILDREN()), "Red", IF(OR(CONTAINS("Red", DESCENDANTS()), CONTAINS("Yellow", CHILDREN())), "Yellow", IF(CONTAINS("Blue", CHILDREN()), "Blue", "Green")))
-
Hi Paul,
It works if manually selected. The challenge now is how to automate this to complete automatically combining both formulas I'd say.
=IF([Start Date]@row = "", "", IF([Start Date]@row > TODAY(), "Blue", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green"))))
=IF(CONTAINS("Red", CHILDREN()), "Red", IF(OR(CONTAINS("Red", DESCENDANTS()), CONTAINS("Yellow", CHILDREN())), "Yellow", IF(CONTAINS("Blue", CHILDREN()), "Blue", "Green")))
As I will be dragging this down on health column to cover everything (phase, milestone and tasks).
Thoughts?
Thanks in advance
-
This is actually pretty straightforward believe it or not. All we need to do is nest them in an IF statement that checks to see whether or not the row is a parent row.
=IF(COUNT(CHILDREN()) > 0,
This basically says "If this is a parent row"
.
=IF(COUNT(CHILDREN()) > 0, formula for parent rows, formula for child rows)
.
Insert the parent row formula into the "if true" portion and the child row formula into the "if false" portion of the IF statement, and you should be squared away.
-
Hi Paul
I end up creating the following formula
=IF([Start Date]@row = "", "", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = 0), "Blue", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(OR(CONTAINS("Red", DESCENDANTS()), CONTAINS("Yellow", CHILDREN())), "Yellow", IF(CONTAINS("Green", DESCENDANTS()), "Green", "Green")))))
This seems to be working as expected.
Rationale:
IF any task is red the milestone becomes yellow (this is to highlight that something is wrong with at least 1 of the 'Children')
IF any milestone is red then the phase becomes yellow (same principle as above)
Issue:
Because my formula is based on dates, if any task has a future date but is completed and I have a late task that is red, the milestone will be yellow as one of the children is red. However, I believe the logic should be based on 'Open" tasks not on all Children as this can mislead the information and health check purpose, BUT I haven't managed to think on something else.
Added a screenshot to ilustrate.
Thanks!
-
This function has a dangerous admiral ackbar level trap.
the count(chikdren()) function as referenced above does indeed detect a parent row but only if the children of that column is populated. The count function will not count blanks.
so you will need to ensure the parens references a column that is non blank like a primary key for the row.
if(count(children()), ‘>’ 0 doesn’t work if the children are blank.
instead use if(count(children([column name]@row)) ‘>’ 0
for some reason I can’t put the greater than symbol in without the quotes. Don’t use quotes for the greater than symbol in your function.
-
@Jeff Fike You are correct that in certain circumstances using just COUNT(CHILDREN()) could backfire, but in the case of Thaigo's solution, there should not be any blank children. I didn't want to add anything that wasn't needed.
In the case that there is the possibility of having blank children there are two different options.
The first would be using your suggestion of referencing a different column, but you would need to ensure that whichever column you are referencing does not have any blanks in it which brings us back to your original concern.
To avoid it altogether you can count both blank and non-blanks like so:
COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")) > 0
