Is It Possible / Formula Request

Felicia Nabors

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

Mark.poole

@Felicia Nabors

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

Felicia Nabors

@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

@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

Mark.poole

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

Felicia Nabors

@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

@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

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.

Felicia Nabors

@Mark.poole

Thanks. I will try that.

Have a great weekend.

Mark.poole

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

Felicia Nabors

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.