Is It Possible / Formula Request
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

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.

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  CompleteHere is my thinking
Initiative  only green if all objectives status' are complete.
Objective  only green if all key results status' are completeOther 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.

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.

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.

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.

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.

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 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.

Hi Mark,
Please forgive the delay in responding. This did not quite work for me. I ended up inserting additional columns and using an if/avg formula to get me the results I needed.
Thanks for all your help.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 220 Industry Talk
 458 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!