Rolling up Health to the Parent row

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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")))))))

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")))))

  • Hi @Lauren T

    I've answered you on the comment you left on this post: https://community.smartsheet.com/discussion/comment/239978#Comment_239978

    I'll re-post the same answer here, in case anyone else has the same question.


    It looks like you just have some parenthesis in the wrong order - try this:

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


    Keep in mind that the order is important since logic formulas read left-to-right. For example, in your formula you have the rule that if the Percent Complete is 100% OR if the Start Date is in the future, the ball will turn green. Then you have a rule that if the Status is "Not Started" the ball will turn Gray. This means that if a row has the date in the future and it says "Not Started", the Green ball will be the one returned since it's the first rule.

    I'll break it out to make sure it says what you want it to:



    =IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green",

    If either the % Complete is 100 OR if the Start Date is in the future, return Green


    2.

    IF([email protected] = "Not Started", "Gray",

    If the Status is "Not Started", return Gray.


    3 .

    IF(AND([Finish Date]@row = TODAY(), [% Complete]@row < 0.5), "Red",

    If the Finish Date is Today AND the Percent Complete is less than 50%, return Red. (You can change out the AND for OR, if you prefer).


    4 .

    IF([% Complete]@row < 0.75, "Yellow",

    If the Percent Complete is less than 75%, return Yellow


    5 .

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

    If the Finish Date is exactly 7 days from now, AND the % Complete is less than 25%, return Red. (You may want to change the = to be <=, less than or equal to, to indicate within the next week instead of exactly 7 days from now).


    6 .

    IF([% Complete]@row < 0.5, "Yellow",

    If it doesn't match any of the criteria above, and the Percent Complete is less than 50%, return Yellow.


    7.

    "Green"))))))

    Finally, if none of the above criteria are met, return Green.


    Let me know if you have any questions about how this was built, or how to adjust the criteria.

    Cheers,

    Genevieve

Sign In or Register to comment.