Rolling up Health to the Parent row

Rolling up Health to the Parent row

Lauren TLauren T
edited 02/13/20 in Using Smartsheet

Is there a way to roll up health of tasks underneath the parent to reflect an "average" health, if you will?


I have a formula for my children to render health, however, I can't seem to roll up to the parent. Would the same formula work the same in the parent row, if my formula is based on start/finish date and % complete?


My health formula is: =IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([email protected] = "Not Started", "Gray", IF([Finish Date]@row < TODAY(), "Red", IF([Finish Date]@row = TODAY(), IF([% Complete]@row < 0.5, "Red", IF([% Complete]@row < 0.75, "Yellow", "Green")), IF([Finish Date]@row = TODAY(3), IF([% Complete]@row < 0.25, "Red", IF([% Complete]@row < 0.5, "Yellow", "Green")))))))

Best Answers

Answers

  • I am trying to a formula to measure health at the task level. I figured it out, and it worked perfectly, but realized I wanted to add in the IF statement that IF Status = Not Started, Health would be Gray. However, with the addition to the formula below, it is returning "Incorrect Arguement Set". Any ideas on what part of the formula is incorrect? Thank you for any help!


    =IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([email protected] = "Not Started", "Gray"), IF([Finish Date]@row = TODAY(), IF([% Complete]@row < 0.5, "Red", IF([% Complete]@row < 0.75, "Yellow", "Green")), IF([Finish Date]@row = TODAY(7), IF([% Complete]@row < 0.25, "Red", IF([% Complete]@row < 0.5, "Yellow", "Green")))))

  • @Genevieve P


    Thank you SOOOOO much! And thank you for breaking it down, it makes so much sense to me now that you've broken it down line by line.

    Seeing it broken out and what each section means, it looks like I'm not getting exactly what I need. I need the following criteria met, I'm just not sure how to write the formula correctly:

    • If Status = Not Started AND Start Date is in the future, Grey
    • If % Complete is equal to or greater than 75% AND "Due Date" is in 1 week, then Green
    • If % Complete is equal to or less than 50% AND "Due Date" is in 1 week, then Yellow
    • If % Complete is equal to or less than 25% AND "Due Date" is in 1 week, then Red
    • If none of the criteria above are met, then Green

    I'm basically wanting to set the health of my project based on the due date compared to the % complete, is this is right logic? Thanks so much for your help.


    Lauren

  • Hi Lauren,

    Since Logic formulas read left-to-right, I've reorganized the order (otherwise the rule for Yellow to look at "less than 50" would include the red ones as well. This is why I've put the Red criteria first).

    Try this:

    =IF(AND([Start Date]@row > TODAY(), [email protected] = "Not Started"), "Gray", IF(AND([% Complete]@row <= 0.25, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Red", IF(AND([% Complete]@row <= 0.5, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Yellow", IF(AND([% Complete]@row >= 0.75, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Green", "Green"))))


    Keep in mind that you have no statement for anything between 50 - 75%... so since your default is Green, anything between that with a due date this week will be Green.


    Here is each statement in comparison to (& in the order of) your points above:


    • If Status = Not Started AND Start Date is in the future, Grey

    =IF(AND([Start Date]@row > TODAY(), [email protected] = "Not Started"), "Gray",


    • If % Complete is equal to or greater than 75% AND "Due Date" is in 1 week, then Green

    IF(AND([% Complete]@row >= 0.75, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Green",


    • If % Complete is equal to or less than 50% AND "Due Date" is in 1 week, then Yellow

    IF(AND([% Complete]@row <= 0.5, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Yellow",


    • If % Complete is equal to or less than 25% AND "Due Date" is in 1 week, then Red

    IF(AND([% Complete]@row <= 0.25, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Red",


    • If none of the criteria above are met, then Green

    "Green"))))


    Let me know if this works!

    Cheers,

    Genevieve

  • @Genevieve P

    It works great! The only thing is that is the due date has passed, it doesn't matter the % complete - it remains Green. Is that because of the last bullet point, where if the criteria is not met above, then Green? Would it change if I take it out? Would a "If (due date)@row is passed, "Red" work?

    Also, I am trying this on a project where the only % complete numbers will be 100% or 0% - how do I add that logic in? Or will I need to come up with a whole other formula?

    Thank you again for your continued help, this is saving me as I'm terrible at formulas!

  • Hi, I posted this in the other thread as well.

    @Genevieve P

    Can you please tell me what column names I need for this to work?

    Do I need anything else for this formula to work?

    Also, do I paste this formula in the health column?


    Thank you!

    Candace

  • Hi @Candace Hebert

    It may be better to write your own formula with specific rules based on the criteria you want to see... but here are the column names that are used in this formula:

    There's a Start Date column

    and a Due Date column

    and a % Complete column.


    This would be pasted into the Health column, or whichever column is returning the RGY balls. This formula accounts for a Gray ball as well.

    Let me know if it works for you, or if you want to adjust any of the criteria that Lauren had specified and I'm happy to help! It may also be useful to see a screen capture of your sheet (but please block out any sensitive data).

    Cheers,

    Genevieve

  • Thanks, @Genevieve P!


    I've attached a screenshot of my sheet. I've used the formula you provided but it says UNPARSABLE.


    Can you please help?


  • Hi @Candace Hebert

    No problem, happy to help! It looks like you've pasted the formula into the % Complete column. Since we're referencing that column in the formula, we want to actually paste this into a new column... one that is a Symbol type of column (see here) that has the different coloured balls as the output. You could call this column "Health" or "Status". Does that make sense?

Sign In or Register to comment.