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 lowercase 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 lefttoright 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 lowercase 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 lefttoright 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!