RYG Formula help!!!

Carla S

I am trying to update RYG using Variance (baseline end - Finish).

1. If Status is complete Gray
2. If Variance = 0 Green
3. If Variance =>-1 and <= -5 Yellow
4. 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

Brent Wilson

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 Wilson

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

Carla S

Great. Thank you! Now on the the child and parent level status :)