Issue with Health Symbols

gmerits

Assume a single parent cell with two children. Here is the formula I am using:

IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue")))

The issue is the Green and Blue. If any of two children are blank and I have a single Green (or Blue), the parent cell is Green (or Blue), when in fact what I need is for both children to be Green (or Blue) before the parent becomes Green (or Blue).

I would also like to add that if one is Green and one is Blue then the status should be Green.

Can anyone help?

Shubham

Hi gmerits, 

You can try the below formula satisfying your required conditions- 

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = 2, "Green", IF(COUNTIFS(CHILDREN(), "Blue") = 2, "Blue", IF(AND(CONTAINS("Green", CHILDREN()), CONTAINS("Blue", CHILDREN())), "Green"))))) 

Thank you.

gmerits

I like the solution but should have mentioned the example I used with 2 cells should be generalized to x cells, so I know that I likely will need to do some kind of on AND(IF statement that looks for "" in a cell to see if it is blank. I have tried everything I know to try but keep getting parsing errors and other types of errors. So I need way to detect a blank cell and take some action if it is blue.

So changing my original statement from "If any of two children are blank" to "if any of the children are blank" would have been a better way of stating the question.

So let's assume x cells then:

1. I any yellow children then yellow parent regardless of blank children.
2. All children must be green with no blank children in order for the parent to be green, otherwise it should be yellow if there is green children but even a single blank child or more.
3. Same goes for Blue.
4. Any red child regardless of blank children should lead to a red parent.
5. Any yellow child and no red children would lead to a yellow parent with or without blank cells.
6. A Blue child or children mixed with a Green child or children and no blanks would lead to a Green parent.

Thanks!

Paul Newcome

Try this:

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(COUNTIFS(CHILDREN(), "Green") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Green", IF(COUNTIFS(CHILDREN(), "Blue") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Blue")))

gmerits

This almost worked, but if I put Blue in a child cell, for example, and the other child cell (or cells if I have more than just 2 cells) is blank, the parent cell does not see a blue symbol. Same with Green.

Thanks!

Paul Newcome

I thought you only wanted it if all children were?

"The issue is the Green and Blue. If any of two children are blank and I have a single Green (or Blue), the parent cell is Green (or Blue), when in fact what I need is for both children to be Green (or Blue) before the parent becomes Green (or Blue)."

gmerits

No, what I am looking for really has to do with dealing with blank cells that are children. I already have it setup so that if any child is yellow the parent is yellow and regardless of blank children or not. Same for red.

It is blue and green. If blue with any blank children, then the parent should be yellow. If red with any blank children, then the parent should be red. If green and blue children and one or more blanks, then the parent should be yellow. The finally, if all green children then green parent and if all blue children then blue parent. Hope that makes sense.

Thanks!

Paul Newcome

What if there are green and blue children with no blanks?