RYG Formula help!!!
I am trying to update RYG using Variance (baseline end  Finish).
 If Status is complete Gray
 If Variance = 0 Green
 If Variance =>1 and <= 5 Yellow
 If Variance >5 Red
=If(Status@row = "Complete", "Gray", If(Variance@row =>"0", "Green", If(Variance@row =>"1", if(Variance@row <="5", "Yellow", If(Variance@row >"5", "Red")))))
Return is #Invalid Operation
Best Answer

First, we have to look at your cases.
I think you have both the negatives, and the less than/ greater confused.
I understand you say if the variance is "more" than 5 off, then it is RED.
That's how you say it in language, but in reality, for it to be "more" Variance than 5, it would be 6 or 8. Those are less than 5, not greater.
It appears you do the same with the yellow case
So I think you are saying this
Also, you are using mathematical symbols like < or to text by stating "5" it should be just 5. This cannot be evaluated.
So try this
=IF(Status@row = "Complete", Gray, IF(Variance@row >= 0, "Green", IF(Variance@row < 5, "Red", "Yellow")))
A little side note, I like to say nested if statements are like cutting a block of cheese. If you have cut off the green area, there is no need to test that a value is not in the green area. So you do not need an AND test for the yellow.
The way I structured this is to check for completion. Then check for green, then check for red and as a result, everything else must be yellow.
You could do it green > yellow > red.. but as I stated, it would also require an AND statement.
=IF(Status@row = "Complete", Gray, IF(Variance@row >= 0, "Green", IF(AND(Variance@row < 1, Variance@row >= 5), "Yellow", "Red")))
Also, note in your logic 0.5 has no colour, as your logic says everything greater than zero is green, and everything less than 1 is yellow.
My first statement will have that as yellow, but the second will class it as Red
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
Answers

First, we have to look at your cases.
I think you have both the negatives, and the less than/ greater confused.
I understand you say if the variance is "more" than 5 off, then it is RED.
That's how you say it in language, but in reality, for it to be "more" Variance than 5, it would be 6 or 8. Those are less than 5, not greater.
It appears you do the same with the yellow case
So I think you are saying this
Also, you are using mathematical symbols like < or to text by stating "5" it should be just 5. This cannot be evaluated.
So try this
=IF(Status@row = "Complete", Gray, IF(Variance@row >= 0, "Green", IF(Variance@row < 5, "Red", "Yellow")))
A little side note, I like to say nested if statements are like cutting a block of cheese. If you have cut off the green area, there is no need to test that a value is not in the green area. So you do not need an AND test for the yellow.
The way I structured this is to check for completion. Then check for green, then check for red and as a result, everything else must be yellow.
You could do it green > yellow > red.. but as I stated, it would also require an AND statement.
=IF(Status@row = "Complete", Gray, IF(Variance@row >= 0, "Green", IF(AND(Variance@row < 1, Variance@row >= 5), "Yellow", "Red")))
Also, note in your logic 0.5 has no colour, as your logic says everything greater than zero is green, and everything less than 1 is yellow.
My first statement will have that as yellow, but the second will class it as Red
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com

Great. Thank you! Now on the the child and parent level status :)
Help Article Resources
Categories
Check out the Formula Handbook template!