RAG Roll-up formula

Emmanuel Ajayi

Could anyone help me with a formula I'm trying to perfect.

I am trying to create a formula that automatically captures RAG status for Children Rows and how this is communicated to the Parent rows.

The plan is essentially structured like this (images are attached):

• Project Plan Summary (Senior parent)
  • Main Tasks (Parent)
    • Activities (Children)
  • Main Tasks 2 (Parent)
    • Activities 2 (Children)

The formula I am using to count the children activities to populate the RAG status for the "Main Tasks" is:

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

*This works fine as I want Red to always be flagged up no matter what*

The formula I have for the "Project Plan Summary" is:

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

The formula above is meant to pick the mode from the "Main Tasks" and if there are two then pick the worst case to be flagged up.

What I need help with in particular is that if there are no status symbols in the "Main Task" rows (i.e they contain " ") I want the "Project Plan Summary" to also be blank.

If anyone could offer some insight I would be very appreciative, even if you can't thanks for reading my long post.

 

 

Paul Newcome

Try something like this...

 

=IF(COUNTIFS(CHILDREN(), ISBLANK(@cell) = COUNT(CHILDREN([Primary Column]@row)), "", insert_original_formula_here)