I have a parent row that needs to populate with appropriate 'Status' based on it's children

PeggyLang

I have many parent rows with many children, in some cased grandchildren. Each child (or grandchild) has a status. Status choices are;

• Not Applicable
• Not Started
• In Progress
• Complete

I have to account for the following possibilities;

I have been able to write a formula for the first 4;

I am having great difficulty writing the remaining 3 (#'s 5, 6 & 7) IF statements. As well, I'm not entirely sure where they should be inserted.

Any help would be and always is GREATLY appreciated.

Paul Newcome

One thing to keep in mind with nested IF statements is that it will stop on the first true value. This means that to get to the second IF, the first must be false. To get to the third IF then the first and second must be false.

It also looks to me like you want to essentially ignore the "Not Applicable" unless all are "Not Applicable". This means you can (for example) combine 2 and 6 into a single IF to say that if the count of "Complete" and "Not Applicable" is equal to the count of children then output "Complete". You can also combine 3 and 7.

So if we leverage this logic we can actually say something more like this:

If all are "Not Applicable" then output "Not Applicable".

If all are either "Not Applicable" or "Complete" then output "Complete".

If all are either "Not Applicable or "Not Started" then output "Not Started".

Every other combination would output "In Progress".

=IF(COUNTIFS(CHILDREN(), @cell = "Not Applicable") = COUNT(CHILDREN()), "Not Applicable", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Applicable", @cell = "Complete")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Applicable", @cell = "Not Started")) = COUNT(CHILDREN()), "Not Started", "In Progress")))

Paul Newcome

Happy to help. 👍️

Honestly... My take on formulas and solutions is driven more by experience with being lazy. Haha. I don't want to work any harder than I really need to, so I am always evaluating formulas and whatnot to try to find the most efficient method to accomplish my goals.

Heather_Verde

They would need to be inserted at the beginning of your formula because you are looking at multiple criteria and then you would flow to the single criteria if the multiple criteria was not met. If you keep the single at the beginning, then you will never make it to the multiple because the single options would have already been met and the formula executed from that point.

PeggyLang

@Heather_Verde your explanation makes no sense to me as there would only be one scenario for any individual situation.

Heather_Verde

Ok. I have just found that when I do formulas, they tend to work better when I have the multiple condition items first. Sorry if it doesn't make sense.

PeggyLang

@Andrée Starå I'm wondering if you have any ideas on this question?

Paul Newcome

One thing to keep in mind with nested IF statements is that it will stop on the first true value. This means that to get to the second IF, the first must be false. To get to the third IF then the first and second must be false.

It also looks to me like you want to essentially ignore the "Not Applicable" unless all are "Not Applicable". This means you can (for example) combine 2 and 6 into a single IF to say that if the count of "Complete" and "Not Applicable" is equal to the count of children then output "Complete". You can also combine 3 and 7.

So if we leverage this logic we can actually say something more like this:

If all are "Not Applicable" then output "Not Applicable".

If all are either "Not Applicable" or "Complete" then output "Complete".

If all are either "Not Applicable or "Not Started" then output "Not Started".

Every other combination would output "In Progress".

=IF(COUNTIFS(CHILDREN(), @cell = "Not Applicable") = COUNT(CHILDREN()), "Not Applicable", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Applicable", @cell = "Complete")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Applicable", @cell = "Not Started")) = COUNT(CHILDREN()), "Not Started", "In Progress")))

PeggyLang

@Paul Newcome I have been struggling with this for DAYS!!!!!

THANK YOU, THANK YOU, THANK YOU!!!!!

Can you recommend some articles, videos, teachings that will help me to see things the way you did?

Paul Newcome

Happy to help. 👍️

Honestly... My take on formulas and solutions is driven more by experience with being lazy. Haha. I don't want to work any harder than I really need to, so I am always evaluating formulas and whatnot to try to find the most efficient method to accomplish my goals.

Oliver Lang

@Paul Newcome Thank you for your contribution, I totally agree with Peggy: THANK YOU!