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”))))))

Find more posts tagged with

Accepted answers

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")))))

All comments

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

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

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 😒

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.