Formula help on RYG Balls for Baseline Variance

dkariotakis

hello all

I'm struggling with this because the variance is in Days. I did a basic IF statement where:

if the Status = complete then Green,

and "nested if" if the Variance was < -5 then Red else Yellow.

It just turned complete things Green and everything else yellow, because the variance is in Days (eg. 1d or -2d not a read number)

Ultimate Goal is to put the health of the project on a report then Dashboard for Executive lead team. Schedule Variance is my first thought.

Thanks for any guidance!

dojones

It's seeing the Variance as text. Create a helper column and use this formula:

=VALUE(LEFT(Variance@row, FIND("d", Variance@row) - 1))

Then base your IF statement on the formula. You could also build the formula into your IF statement to avoid the helper column.

dkariotakis

This is the logic I couldn't figure out how to build in a helper column. Thanks!

dkariotakis

actually, it's not working. it seems the parenthesis aren't adding up. I tried just added a closing paren, but that didn't work thoughts?

dkariotakis

nevermind, it's not the parenthsis. but it's still not working

dojones

Try this.

Health formula is:

=IF(Status@row = "Complete", "Green", IF(Variance@row < -5, "Red", "Yellow"))

Tag me @dojones in response if you want me to see it.

dkariotakis

I have it sorted out @dojones appreciate the feedback! I did end up with a helper column, because I have to re baseline occasionally. Thanks!!