I need help with the RYGB Status Balls formula

mmajor76

Hello,

I am trying to calculate health status (RYGB) for each task and milestone. Red= Not Started and past due, Yellow, In progress, but % complete is equal to or lower than 48% (.48), Green is in progress, % complete is equal to 60% or greater and blue is completed. I have read through many posts, but cannot seem to find the right one to help with the formula I need. Any one have a formula they are using for this? My columns are Duration, Start Date, End Date % Complete and Health. Thank you.

Jennifer Kurtz

Hi @mmajor76 !

I like to use a formula to set my status indicator (RYGB) column as well! I think you could write an IF formula something like the one below. FYI that I've assumed the % complete is displayed as a percentage but should be decimalized in the formula.

=IF([% Complete]@row = 1, "Blue", IF(OR([% Complete]@row = 0, [End Date]@row < TODAY()), "Red", IF([% Complete]@row <= 0.48, "Yellow", IF([% Complete]@row >= 0.6, "Green"))))

One more note on this: You've identified "Yellow" as less than or equalf to 48%, and Green greater than or equal to 60%. As such, the Health column will be blank for anything between 0.48-0.60.

Hope that helps!

mmajor76

Thank you, @Jennifer Kurtz!

mmajor76

@Jennifer Kurtz , I just realized based on your answer that I did leave out some information for Red, Not Started, and past due =0 or Red, Started, Past due, % complete is .48 or less. Also, I guess I need to adjust my Green for In progress, % complete is .60 or greater and not past due. If past due, then Red. Also, what are your thoughts for on accounting for the gap I have with the .48-.60?

Thank you.

Jennifer Kurtz

@mmajor76 - One thing that's helpful to keep in mind with IF statements is that Smartsheet reads the formula from left to right and essentially "stops" once it finds a "true."

In the example formula I wrote, it isn't necessary to say that Green isn't past due. The formula would catch any "past due" items first, and then stop. So it's assumed already that Green isn't past due. Make sense?

As for the gap you have between 48%-60% complete, I'd recommend adjusting your ranges for % complete to include all percentages. How you do that depends on what you're trying to communicate with the Health Status. You could consider making Yellow to be less than 0.60, and keep Green as equal to or greater than 0.60. Or, you could adjust Green to be greater than 0.48. :)

Does that help?

mmajor76

Great, Thank you again. Appreciate the insight and help.