# RYG formula question

✭✭✭✭✭

I need some help with tweaking the below:

=IF(OR([Actual Completion Percentage %]@row = 1, [Schedule Variance]@row >= 0.9, AND([Actual Completion Percentage %]@row >= 0, [Baseline Start]@row > TODAY())), "Green", IF(OR([Schedule Variance]@row >= 0.8, IF(OR([Schedule Variance]@row < 0.9, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Yellow", AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Red", IF(OR([Schedule Variance]@row < 0.8, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Red", IF(AND([Actual Completion Percentage %]@row = 0, [Baseline Start]@row > TODAY()), "")))))

I am trying to state: Green is greater than 0.9, yellow is between 0.8 and 0.9, and red is anything less than 0.8

• Employee

I find it helps to break out IF statements into individual "sentences" to see where there may be an issue. The way I do this is by putting each "IF" on its own line.

For example, I did this with yours and bolded where I think there may be a mixup of logic statements:

=IF(OR([Actual Completion Percentage %]@row = 1, [Schedule Variance]@row >= 0.9, AND([Actual Completion Percentage %]@row >= 0, [Baseline Start]@row > TODAY())), "Green",

IF(OR([Schedule Variance]@row >= 0.8,

IF(OR([Schedule Variance]@row < 0.9, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Yellow",

AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Red",

IF(OR([Schedule Variance]@row < 0.8, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Red",

IF(AND([Actual Completion Percentage %]@row = 0, [Baseline Start]@row > TODAY()), "")))))

=IF(OR([Actual Completion Percentage %]@row = 1, [Schedule Variance]@row >= 0.9, AND([Actual Completion Percentage %]@row >= 0, [Baseline Start]@row > TODAY())), "Green",

IF(AND([Schedule Variance]@row >= 0.8, [Schedule Variance]@row < 0.9, [Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Yellow",

IF(OR([Schedule Variance]@row < 0.8, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Red",

IF(AND([Actual Completion Percentage %]@row = 0, [Baseline Start]@row > TODAY()), ""))))

Let me know if that solved the problem!

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Moderator

For the statement at the bottom of your post, you can try with =IF([Schedule Variance]@row < 0.8, "Red", IF([Schedule Variance]@row < 0.9, "Yellow", "Green")) and then add complexity to the formula with additional conditions as needed.

I hope this can also be of help!

Julio

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!