RYG based on cell value


I'm trying to set the RYG ball based on the value of a cell and I can't seem to get it right. If the value is:

Greater than 0 = Green

Between 0 - 0.5 = Yellow

Less than 0 = Red

=IF(Remaining@row > 0, "Green", IF((Remaining@row >0, AND(IF(Remaining@row<0.50,))), "Yellow", IF(Remaining@row<0), "Red")))


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Have you confirmed the Yellow is in fact populating as expected? Usually a nested IF reads left to right and stops on the first true value. Since (for example) 0.25 is greater than 0, it will populate the Green output and not make it to the Yellow argument at all.

    If that happens to be the case, can you be more clear about the requirements you have for each of the colors since anything that is between 0 and 0.5 will always go to Green first? Do you mean that anything above 0.5 should be Green, 0 - 0.5 would be Yellow, and anything less than 0 would be Red?

    =IF(Remaining@row< 0, "Red", IF(Remaining@row<= 0.5, "Yellow", "Green"))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!