Need help with RYG status formula

Dawn Skirvin

=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

Genevieve P.

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

Genevieve P.

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

Dawn Skirvin

Thanks so much!!

Genevieve P.

No problem! Glad it worked for you 🙂