Flagging a Parent if % of Children are at Risk

spopres

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.

spopres

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

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

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

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

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

Great to hear that it's working