Ancestor Formula for RYG

Elizabeth Riebeling

Hello,

I am trying to create a Status for the Overall Project using the Ancestor formula based the Parent-Child status. It is NOT based on %. I cannot seem to make it work. It works well for the Parent-Child but then when i try to go a level up it is Unparseable :(

This is what I have at the Parent level

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

Any suggestions/help will be appreciated!

Genevieve P.

Hi @Elizabeth Riebeling

Can you post a screen capture of your sheet and the levels, but block out sensitive data?

The formula you have there should work on higher levels, however the "Children" of a higher level would be a "Parent" row of other rows, does that make sense?

So if you had 1 Grandparent row, 3 Parent rows, and 10 Child rows, the formula in the Grandparent Row would only look at the 3 Parent row cells. However those 3 Parent rows will have their formula based on the Child rows.

Do you want something different for the higher levels?

Elizabeth Riebeling

Hi and thank you for responding!

It makes sense but i don't know how to make it hone in on ONLY the parent rows

This is not thr RYG but same concept

Children Rows Screenshot.PNG

Genevieve P.

Hi @Elizabeth Riebeling

If you put the same formula in a "Grandparent" row then it will hone in on only the Parent rows (since those are the Children of the Grandparent). Is that what you're looking to do?

Using your screen capture, you should be able to place this formula in the "Project Charter" row and it will look at the two darker blue rows.

Then if you put the exact same formula (no edits needed) in the "Initiation Phase" row, it will look at the "Project Charter" and "Identify Stakeholders" levels.

If this isn't what you want it to do, could you explain how you want the formula to calculate, using the screen capture for reference?