RYG based on cell value

Sam C

Hello,

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

Paul Newcome

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

Mr. Chris

Hi @Sam C

Something like this perhaps?

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

Sam C

That worked, you're awesome. Thank you!

Paul Newcome

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

Sam C

Yes, I adjusted the first formula with a little trial and error, but just needed it as a place to start. Thank you!

Mr. Chris

https://community.smartsheet.com/discussion/comment/416519#Comment_416519

Happy this worked for you. Thanks for the feedback!