Setting up a nested-if formula to automate the RYG status balls

Karen Hitchcock

Can someone please tell me how to set up a nested-if formula to automate the RYG status balls in a "Status" column so that it reflects the % Complete.

If the % Complete column contains a value less than 70% complete, I want the Status column to show a red status call (this is already set up in the Status column). If the % Complete column has a value that is 70% or above and less than 100%, I want the Status column to show a Yellow status ball. And if the % Complete column shows 100%, I want the Status column to show a Green status." status ball

Darla Brown

I think the issue is the Yellow. If you do <=1, 100% will be Yellow. Can you do <1 for Yellow? Then = 1 could be green.

Darla Brown

Hi,

Try this:

=IF([% Complete]@row < .70, "Red", IF([% Complete]@row > .70, "Yellow", IF([% Complete]@row < 1, "Green")

You can then make it a column formula.

Karen Hitchcock

Thank you, Darla! That kinds works. Only issue is with the green. I have a column with a value of 100% for which I want the Status column to show a green ball. I've tried using IF([% Complete]@row = 100, "Green") and that didn't work. I tried IF([% Complete]@row = 1, "Green"). I tried IF([% Complete]@row = 1, "Green").

Darla Brown

I think the issue is the Yellow. If you do <=1, 100% will be Yellow. Can you do <1 for Yellow? Then = 1 could be green.

Karen Hitchcock

Yes! Thank you VERY much.

Darla Brown

Awesome! Happy to help!