Health formula not working

I'm attempting to build a formula to change the health color based on a drop down of 3 options.

My logic is this: If Flu level is less than 5%, turn flu risk level green. If Flu level is between 6% and 9%, turn flu risk level yellow. If Flu level is greater than 10%, turn flu risk level red

The formula I came up with is =IF([Flu level]@row < 0.05, "Green", IF(AND([Flu level]@row >= 0.06, [Flu level]@row <= 0.09), "Yellow", IF([Flu level]@row > 0.10, "Red", "")))

Unfortunately, the less than 5% option will not change to green. Am I missing something?

Appreciate any guidance you can provide.

Best Answer

  • Jennifer Kurtz
    Jennifer Kurtz ✭✭✭✭✭
    Answer ✓

    Correct me if I didn't interpret your set-up correctly — but it looks like you have different text options in the drop down, and that those aren't actually "calculated" percentages. If so, you could rewrite your If statement to read, IF([Flu Level]@row="< 5%", "Green" etc. Does that work?

Answers

  • Jennifer Kurtz
    Jennifer Kurtz ✭✭✭✭✭
    Answer ✓

    Correct me if I didn't interpret your set-up correctly — but it looks like you have different text options in the drop down, and that those aren't actually "calculated" percentages. If so, you could rewrite your If statement to read, IF([Flu Level]@row="< 5%", "Green" etc. Does that work?

  • Thank you, Jennifer. I initially used the text options in the drop down, but it wasn't working. Perhaps it was a glitch in the system? I followed your recommendation and low and behold, it worked! :)

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!