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

Options
✭✭

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
edited 02/29/24
Options

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"))

• ✭✭
Options

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!