Need help with RYG status formula
=IF([% Complete]@ROW<.7, "RED"), IF ([% Complete]@ROW< 1>.7, "YELLOW"), IF([% Complete]@ROW)=1, "GREEN"),"")))
If below/above certain percentages, attempting to change the ball to red/yellow green
Best Answer
-
Hi Dawn,
The first thing to note is that the @row function needs to be lower-case or you'll receive an error. Secondly, you'll want to make sure that the end of your IF statements are left open, without a closing parenthesis ) until the very end of the entire statement.
Then since Logic formulas read left-to-right and stop as soon as the criteria is met, you want to make sure your statements are in the right order. I would start with the Green because it only has one possible value... then if it's not 100, the formula will move on to the next statement.
Try this:
=IF([% Complete]@row =1, "Green", IF([% Complete]@row < 0.7, "Red", IF([% Complete]@row >= 0.7, "Yellow", "")))
Keep in mind that this will return a Red status ball if the cell is blank, since it reads a blank cell as "less than 0.7". You could eliminate that by adding another criteria at the very beginning:
=IF([% Complete]@row = "", "", IF([% Complete]@row =1, "Green", IF([% Complete]@row < 0.7, "Red", IF([% Complete]@row >= 0.7, "Yellow", ""))))
Let me know if you have any questions!
Cheers,
Genevieve
Answers
-
Hi Dawn,
The first thing to note is that the @row function needs to be lower-case or you'll receive an error. Secondly, you'll want to make sure that the end of your IF statements are left open, without a closing parenthesis ) until the very end of the entire statement.
Then since Logic formulas read left-to-right and stop as soon as the criteria is met, you want to make sure your statements are in the right order. I would start with the Green because it only has one possible value... then if it's not 100, the formula will move on to the next statement.
Try this:
=IF([% Complete]@row =1, "Green", IF([% Complete]@row < 0.7, "Red", IF([% Complete]@row >= 0.7, "Yellow", "")))
Keep in mind that this will return a Red status ball if the cell is blank, since it reads a blank cell as "less than 0.7". You could eliminate that by adding another criteria at the very beginning:
=IF([% Complete]@row = "", "", IF([% Complete]@row =1, "Green", IF([% Complete]@row < 0.7, "Red", IF([% Complete]@row >= 0.7, "Yellow", ""))))
Let me know if you have any questions!
Cheers,
Genevieve
-
Thanks so much!!
-
No problem! Glad it worked for you 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!