RYG formula question

Melissa Torrez

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

Genevieve P.

Hi @Melissa Torrez

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()), "")))))

Try this instead:

=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

Julio S.

HI @Melissa Torrez,

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