Formula to set a color of an icon depending on the % brackets

Options

Dear Colleagues,

I am trying a formula with dot icons to conditionally set the color if the budget is exceeded by:

0%<10% a dot remains green

10%>15% a dot becomes yellow

15%> a dot becomes red

I have tried this formula

but my colors are wrong. Please see an example. I suspect that my formula is incorrect because the 0.19% is negligible and -- according to our original design it should still show green.


Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Nikita Che

    There currently is no statement that says that up to 10% should stay Green, right now it just says that 0 = Green.

    Try this:

    =IF([Difference as %]@row <= 0.1, "Green", IF([Difference as %]@row <= 0.15, "Yellow", "Red"))

    This says that if the percent is less than or equal to 10%, show Green. Then if it's over 10% it moves on to the next statement that says if the percent is less than or equal to 15%, show Yellow. If neither of these statements are true then it has to be Red because then it's over 15%.

    Let me know if this works for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Nikita Che

    There currently is no statement that says that up to 10% should stay Green, right now it just says that 0 = Green.

    Try this:

    =IF([Difference as %]@row <= 0.1, "Green", IF([Difference as %]@row <= 0.15, "Yellow", "Red"))

    This says that if the percent is less than or equal to 10%, show Green. Then if it's over 10% it moves on to the next statement that says if the percent is less than or equal to 15%, show Yellow. If neither of these statements are true then it has to be Red because then it's over 15%.

    Let me know if this works for you!

    Cheers,

    Genevieve

  • Nikita Che
    Options

    @Genevieve P. , thank you so much! It worked!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Wonderful! I'm glad I could help. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!