Ignore blanks in formula that averages the color balls on the children tasks

Emilligan

Hi -

I have a formula that seems to work to average the color balls in the children tasks

=IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), "Green", "Yellow"), IF(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow"), "Red", "Yellow"))

When the children cells are blank, the average comes out Yellow which skews the overall health of the project, as I use the same formula to average all the color ball headings.

Ideas on how can I modify this formula to ignore blank cells?

The formula is in Row 13, 14, and 18.

Thanks.

Eric Law

@Emilligan So, I believe the reason why it is yellow isn't because of the blank cells, but it's because of your logic and your resolution for your if false being yellow. To prevent this, I would recommend you add >= instead of just >. That way, if there are the same amount of greens to reds, you will get a green instead of a yellow.

Eric Law

@Emilligan So, I believe the reason why it is yellow isn't because of the blank cells, but it's because of your logic and your resolution for your if false being yellow. To prevent this, I would recommend you add >= instead of just >. That way, if there are the same amount of greens to reds, you will get a green instead of a yellow.

Emilligan

Thanks @Eric Law that makes sense! What about in this case, where the children are blank, but the formula returns green. Is there a way to have it return blank if there are no color balls yet in the children (would represent work not started.) Otherwise the not started work might skew the overall project health...

Using this formula in row 25

=IF(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red"), IF(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), "Green", "Yellow"), IF(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), "Red", "Yellow"))

Emilligan

Figured it out. Added this string at the beginning:

=IF(COUNT(CHILDREN()) = 0, "", 

so

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