Help With Formula
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"))
Answers
-
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"))
-
Ahh thank you for that explanation, Paul. This helps tremendously!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!