Averaging Health Status for Parent Rows based on Average of Children Rows in RYG including BLUE

AHeadlee

Hi-

I'm trying to average children rows with RYG and Blue status where:

Red

• If Red is greater than Yellow
• If Red is greater than Green

Yellow

• If Yellow is greater than or equal to Red
• If Yellow is greater than or equal to Green

Green

• If Green is greater than Red
• If Green is greater than Yellow

Blue

• If only status is Blue

This formula is unparsable and I haven't been able to figure out why:

=IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Blue") = COUNTIF(CHILDREN(), “Blue”))))))

Nick Korna

You have equal numbers of Red and Green status and there is nothing in the formula which says what happens in the event of a tie between Red and Green - you need to make one of the checks for these into equal or greater than in a similar way to the yellow.

If you're happy with the rest of the formula, but want Green to show if there is a tie between Red/Green:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red")), "Yellow")))))

If you want Red to be displayed:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red")), "Yellow")))))

Carson Penticuff

You have the wrong quote characters around Blue.

“Blue” instead of "Blue"

I would also recommend adding an additional condition to your "yellow" IF statement. As it is, if no status is entered for any child rows, the result will be yellow, as yellow will equal both green and red (zero).

AHeadlee

Thank you. That was helpful. I revised my formula based off your feedback to this:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red")), "Yellow")))))

However, I have an odd issue occurring where one of the parent fields is not calculating (See Row 3 under Overall Health). Row 26 and 36 are working fine and I tested them with many different RYGB combination. I've tried clearing the contents of the cell in Row 3 & repasting the formula as well.

Nick Korna

You have equal numbers of Red and Green status and there is nothing in the formula which says what happens in the event of a tie between Red and Green - you need to make one of the checks for these into equal or greater than in a similar way to the yellow.

If you're happy with the rest of the formula, but want Green to show if there is a tie between Red/Green:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red")), "Yellow")))))

If you want Red to be displayed:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red")), "Yellow")))))

AHeadlee

Thank you, @Nick Korna! That worked.

There's always one little thing I miss in formulas 😒