If child return indicator color based on dates but if parent, average indicators from children

Megan MacKenzie Benefiel

I am looking for a formula to use in a status column that returns red, yellow or green based on criteria. Current I have two formulas:

For children without children (lowest level tasks) I need:

=IF(OR([% Complete]@row = 1, Complete@row = 1), "Green", IF(ISBLANK([Start Date]@row), "", IF(State@row = "delayed", "Red", IF(AND([End Date]@row < TODAY(), AND(Complete@row <> 1, [% Complete]@row <> 1)), "Red", IF(AND([End Date]@row - 5 <= TODAY(), [% Complete]@row <> 1), "Yellow", IF([Start Date]@row > TODAY(), "Blue", "Green"))))))

But if a row is a parent (or grandparent) I need it to simply return the most frequent or common indicator from it's children.

I need to achieve this with one formula that can be used in all cells (regardless of parent or child status) because the sheet will be used a template for many people so I can't have people trying to figure out which formula to use based on what type of row they have.

Ultimately what I'm trying to do it make my top most row accurately reflect overall task health, when the tasks themselves indicate health based on their status compared to start and end dates. So if there's an easier way to do this, I'm open to that as well.

L_123

You can do a quick check to see which generation the value is with

=if(count(parent())=0,1,0)

Or check the specific generation via

=if(count(ancestors())=X,1,0)

~X is variable, put whatever number you want there to return that specific generation.

to go a little further, I think what you want is just the most common RYG of the children right?

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

Anyway you just need to put that in the true return of the ancestors check, and your formula in the false return.

=if(count(ancestors())=0,IF(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow"), IF(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), "Red", "Green"), IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), "Green", "Yellow")),IF(OR([% Complete]@row = 1, Complete@row = 1), "Green", IF(ISBLANK([Start Date]@row), "", IF(State@row = "delayed", "Red", IF(AND([End Date]@row < TODAY(), AND(Complete@row <> 1, [% Complete]@row <> 1)), "Red", IF(AND([End Date]@row - 5 <= TODAY(), [% Complete]@row <> 1), "Yellow", IF([Start Date]@row > TODAY(), "Blue", "Green"

Hopefully that helps out.

Paul Newcome

I'd also like to suggest simplifying your child row portion...

IF(OR([% Complete]@row = 1, Complete@row = 1), "Green", IF(ISBLANK([Start Date]@row), "", IF(OR(State@row = "delayed", [End Date]@row < TODAY()), "Red", IF([End Date]@row - 5 <= TODAY(), "Yellow", IF([Start Date]@row > TODAY(), "Blue", "Green")))))

userxyz

https://community.smartsheet.com/discussion/comment/250615#Comment_250615

What does Complete@row <> 1 reference?

userxyz

Would like to use the same formula above

=if(count(ancestors())=0,IF(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow"), IF(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), "Red", "Green"), IF(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), "Green", "Yellow")),IF(OR([% Complete]@row = 1, Complete@row = 1), "Green", IF(ISBLANK([Start Date]@row), "", IF(State@row = "delayed", "Red", IF(AND([End Date]@row < TODAY(), AND(Complete@row <> 1, [% Complete]@row <> 1)), "Red", IF(AND([End Date]@row - 5 <= TODAY(), [% Complete]@row <> 1), "Yellow", IF([Start Date]@row > TODAY(), "Blue", "Green"

with the following setup

Can someone please help ?

Paul Newcome

@userxyz

Complete@row <> 1

Complete@row: The cell in the Complete column that is on the same row as the formula

<>: Not equal to

1: This could be a "true" value for a checkbox or flag column, the actual number 1, or in the case of percentages 100%

So basically it says "The cell in the Complete column on this row is not equal to 1".

userxyz

I'm not able to put it together into 1 line and I've previously asked a forum member here as well but it returns as unparseable.

https://community.smartsheet.com/discussion/71457/ryg-health-and-status-indicator-roll-up