Need to populate health dot based on data in target measurement column

Trying to populate a health dot (red, yellow, green) based off of target column with the below metrics:

Green = 90% or higher

Yellow= greater than 80% but less then 90%

Red= 79% or less

and then another formula

Green= 85 or higher

Yellow= 70-84

Red= 69 or less

I dont even know if this is possible but I figured this is the place to ask


TIA

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    You will need to adjust this to match your column name, but it should work.

    Formula 1:

    =IF(ISNUMBER([Target Column]@row), IF([Target Column]@row < 0.8, "Red", IF([Target Column]@row < 0.9, "Yellow", "Green")))

    Formula 2:

    =IF(ISNUMBER([Target Column]@row), IF([Target Column]@row < 0.7, "Red", IF([Target Column]@row < 0.85, "Yellow", "Green")))


    You will likely be able to see how you can tweak the specific values in these two formulas to set the results to whatever you would like.

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    edited 02/29/24

    Hi @Staci B

    For the formulas below Make sure the column properties that contain the formula are set to "Symbols"

    For this scenario:

    Green = 90% or higher

    Yellow= greater than 80% but less then 90%

    Red= 79% or less

    The following formula should work:

    =IF([Health Column]@row > 0.89, "Green", IF([Health Column]@row >= 0.8, "Yellow", "Red"))

    For the other formula:

    Green= 85 or higher

    Yellow= 70-84

    Red= 69 or less

    =IF([Health Column 2]@row > 84, "Green", IF([Health Column 2]@row >= 70, "Yellow", "Red"))

  • Staci B
    Staci B ✭✭

    Thanks so much for both of your help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!