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

Options

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.


Best Answer

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓
    Options

    @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.

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    Emilligan ✭✭
    edited 09/22/23
    Options

    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
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!