Has anyone developed a "heath status" equation that considers baseline and task weight?

jto32423
jto32423 ✭✭
edited 02/10/23 in Formulas and Functions

Hello, has anyone developed an equation for a "health status" (red, yellow, green balls) that considers both the task status in comparison to the planned start/end dates as well as the weight of that task duration?

I tried playing around with the following logic:

Green: the task is not late and the actual % complete of the tasks is greater than or equal to 90% of the planned (aka baselined) % complete.

Yellow: the task is not late and the actual % complete of the tasks is greater than or equal to 75% of the planned (aka baselined) % complete.

This does not really work though, because the difference between a green and yellow status for a task is 3.5% variation between Actual % Complete and Planned % Complete (and my team typically tracks Actual % Complete in 5% increments because no one is going to say a a tasks is 13.5% complete!). Curious if anyone has a create way to showing "Health Status" in relationship to the baseline.


Thanks!

Answers

  • Ipshita
    Ipshita ✭✭✭✭✭✭

    Hello @jto32423 ,

    Try using the Averaged RYG for IF and COUNTIF formula for the symbols using one of the following logical expressions - (I have selected the one in blue outline for you)

    To do this, add a manual column for symbols with Green, Red, Yellow buttons and the to the next column add the formula to test your logic. Hope this helps. Let me know. :))

    Cheers!!


    Ipshita Mukherjee

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!