Flagging a Parent if % of Children are at Risk

Options

Hi there,

Having trouble flagging my parent if 33% of the children are flagged. Looking to add the formula right in parent "at risk' cell.

I have a few if count / count ifs that do not seem to be doing the trick.

Answers

  • spopres
    Options

    I seem to be getting closer on this, but still a ways away.


    =IF(COUNTIFS(CHILDREN(), 1) > 0, 1)


    Except I want to replace the '0' above with 33% of the number of open tasks.....something like the following:


    COUNTIFS(OR(CHILDREN([Status]), “In Progress”, CHILDREN([Status]), “On Hold”, CHILDREN([Status]), “Not Started”  * .33)


    Uggh...this seems to be getting messy.....

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    I think this might do it:

    =IF(COUNTIF(CHILDREN(Status@row, OR(@cell = "In Progress", @cell = "On Hold", @cell = "Not Started")) / COUNT(CHILDREN()) >= 0.33, true, false)

  • spopres
    Options

    Hmm, still get an Unparseable. Looks like it's missing a ')'. Also, since I want to flag the cell, shouldn't it be 1, 0 instead of true, false?

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    1/0 and true/false are interchangeable. But yes, you can use 1, 0 instead. Try


    =IF(COUNTIF(CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "On Hold", @cell = "Not Started")) / COUNT(CHILDREN()) >= 0.33, true, false)

  • spopres
    Options

    David - I got it to work as intended with a few tweaks to your statement. Yours did work but it wasn't calculating the count of children risks correctly. I added the simple count of children flags, took out an if, changed the divide to multiple. Still testing, but I think this should cover all my basis.

    Appreciate the help....I probably would have been another night or two figuring out the correct @cell notation.


    IF(COUNTIF(CHILDREN(), 1) > COUNTIF(CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "On Hold", @cell = "Not Started")) * 0.33, 1, 0)

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    Great to hear that it's working

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!