What is wrong with my formula?

Question

I'm trying to do multiple IF formulas to show a Green, Yellow, or Red traffic light based on a % complete column. I can get "Red" (<70%) to show by itself, "Green" (100%) to show by itself, but I can't get the yellow range or all three together. What is wrong below?

=IF([% Complete]3 = 1, "Green"), =IF([% Complete]3 <1 AND >=.7, "Yellow"), =IF([% Complete]3 < .7, "Red")

Thank you

SoS | Dan Palenchar · Accepted Answer

Logical statements are evaluated in order and stop once any condition is true, so you don't need the AND statement for your yellow. You can use this

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

The way you wrote your AND statement is incorrect though, the AND should be before the logical statements and each separated by a comma, it would look like this

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

Though it is unnecessary in this case and it is a best practice to keep your formulas as simple as possible.

Dennis Slagle · Accepted Answer

Thank you for the response. I did figure it out but your formula is less complicated and better structured. I tried to correct the order but failed with many different formulas.

All the best,

Dennis