Averaging Health Status for Parent Rows based on Average of Children Rows in RYG including BLUE
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”))))))
Best Answer
-
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")))))
Answers
-
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).
-
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.
-
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")))))
-
Thank you, @Nick Korna! That worked.
There's always one little thing I miss in formulas 😒
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!