If child return indicator color based on dates but if parent, average indicators from children
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.
Answers
-
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.
-
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")))))
-
What does
Complete@row <> 1reference? -
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 ?
-
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".
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
