Is It Possible / Formula Request

Options
Felicia Nabors
Felicia Nabors ✭✭✭✭
edited 05/30/24 in Formulas and Functions

Hi,

I have a Smartsheet that has IOKR's in it (initiative, object, key result) that is not using parent / child rows. Is it possible to create a formula that gives me the health of initiatives based upon the health of the objectives which is based on the health of the key results? One initiative may have multiple objectives.

I have created a formula in my sheet that gives the health based upon due dates and status, but it is giving it for everything and is not accurate for initiative and objective. If I need to separate out the initiative and the object into a separate column I can do that as well. I am including a screenshot of my current sheet.

Thank you for any help.

Felicia

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @Felicia Nabors

    How exactly are you wanting the Health symbols to fall? As it can be done.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Felicia Nabors
    Felicia Nabors ✭✭✭✭
    edited 05/30/24
    Options

    @Mark.poole

    I would like the colors to be same as the current Health Column. Here is the current legend

    Gray - Not Started and/or not due in the next 90 days
    Yellow - In Progress and due in the next 90 days
    Red - Not Started/In Progress and due in the next 30 days
    Green - Complete

    Here is my thinking

    Initiative - only green if all objectives status' are complete.
    Objective - only green if all key results status' are complete

    Other colors would be based upon due dates outlined above, but I would also like to base it upon the majority color of the underlying child. For example if the objective has 5 KR and 3 are yellow but 2 are red and the objective is in the next 90 days the objective should be yellow, but if all 5 KR's are red and the objective is due in the next 90 days, the objective should be red. (I think that makes sense).

    Also, thanks for your help.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24
    Options

    @Felicia Nabors

    Ok. Lets Try This.. I will put os1, os2, os3 and so on because i dont know how many objective status rows you have.

    and kr1,kr2 and so on for key results

    =IF(Or(Status@Row = "Complete",And([Item Type]@row="Initiative",[os1]@row = "Complete",[os2]@row = "Complete",[os3]@row = "Complete"),And([Item Type]@row = "Objective",[kr1]@row = "Complete",[kr2]@row = "Complete",[kr3]@row = "Complete")),"Green",IF(And(Status@Row= "Not Started", [End Date/Due Date]@row >Today(90)),"Grey",IF(And(Or(Status@Row = "In Progress",Status@Row = "Not Started"),[End Date/Due Date]@row <=Today(30)),"Red",IF(And(Status@Row = "In Progress",[End Date/Due Date]@row <=Today(90)),"Yellow"))))

    Just Replace the OS and KR rows with yours

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    Outside of seeing if it works for you. The problem I can see running into is when you don't have an entry in any of your Objectives or Key Results. So we would have to account for the blanks as well. I will do that once i am sure this portion of the formula works for you until that point

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Felicia Nabors
    Felicia Nabors ✭✭✭✭
    Options

    @Mark.poole

    I think it might work. I have to rework it to include the Helper Reports column because it has the Objective and Key Results numbers in it. I will update this post by tomorrow morning and let you know if it worked for me. Thank you for working this out and pointing me in the right direction.

  • Felicia Nabors
    Felicia Nabors ✭✭✭✭
    Options

    @Mark.poole

    Hi. After working on the formula I determined that I might be overcomplicating what I am trying to do. So, below is my new formula, not sure it will work long term, I am still testing.

    =IF(AND([SP Alignment (Pillar)]@row = "F1", [Item Type]@row = "Initiative", OR([Health]1 = "Gray", [Health]2 = "Gray", [Health]3 = "Gray")), "Gray", "")

    Now, I am trying to figure out is it possible to write a formula for Smartsheet to calculate the correct symbol based upon the health status color and number of items. For example if I have a total of 5 items and the Health Status of 4 is red, and 1 status gray, could I write a formula that would determine that my Objective Status is red as the majority of the items are in a critical stage?

    Thanks again for your help.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/31/24
    Options

    hard to truly think right now. How ever if you create a reference sheet that assigns a numeric value to each color. You can then Write a sum function with index match integrated that calculates the totals and assign a color based on the value of said totals.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Felicia Nabors
    Felicia Nabors ✭✭✭✭
    Options

    @Mark.poole

    Thanks. I will try that.

    Have a great weekend.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @Felicia Nabors Let me know how it works out for you.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!