Help With Formula

Nikki Seador

I really struggle with formulas. I am trying to create a nested if formula to automate RYG in a status column. So I need to show the following.

If % complete is less than 70% turn the status red, if it's 70% or more turn status yellow, if it's 100% complete turn status green. This is what I have, any help would be greatly appreciated. Hopefully this isn't too far off, like I said, I struggle with these. Thanks!

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

Paul Newcome

Close. Just keep in mind that nest IF statements work from left to right and stop on the firs "true" value. That means that since 1 is greater than .7 it will generate a "true" for the "Yellow" and never progress to the green. Additionally. You have less than .7 and greater than .7, but you don't have anything covering .7 exactly. Based on your post above, you would want to use greater than or equal to for the "Yellow". But... If we already specify Green and Red, then we don't even need to establish any criteria for Yellow. If it makes it past Green and Red, then by default it must be greater than or equal to .7 but also less than 1 since it did not trigger a "true" for the first two IFs.

Give this one a go...

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

Using the same logic of working from left to right, this would produce the same exact results...

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

Nikki Seador

Ahh thank you for that explanation, Paul. This helps tremendously!

Paul Newcome

Happy to help. 👍️