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

Options

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

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 05/18/20
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    userxyz ✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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
    userxyz ✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!