Health of Work/Color Formula Question

Bianca Barrow

Hello,

I am new to Smartsheet and have no idea on how to properly articulate the following rules in a formula.

I am using the colors RYGB and following statuses:

• New = green, if end date is one week away yellow, If end date has passed red

• In progress = if % complete is between 80-90% and not overdue green, otherwise yellow if % complete is <80% and end date is <1 week away; 

• Blocked = red

• Review = green, if end date is one week away yellow and % complete is <90%, If end date has passed red

• Done = blue 

Leibel S

@Bianca Barrow

your description does not seem so clear, but below is a rough idea of the formula you need, play around with it (change columns name as needed)

=IF(Status@row = "Done", "Blue", IF(Status@row = "Blocked", "Red", IF(Status@row = "New", IF(TODAY() > [End Date]@row, "Red", IF(([End Date]@row - TODAY()) <= 7, "Yellow", "Green")), IF(Status@row = "In progress", IF(AND([% Complete]@row >= 0.8, [% Complete]@row <= 0.9, TODAY() > [End Date]@row), "Green", IF(AND([% Complete]@row < 0.8, ([End Date]@row - TODAY()) <= 7), "Yellow", "Red")), IF(Status@row = "Review", IF(TODAY() > [End Date]@row, "Red", IF(AND([% Complete]@row < 0.9, ([End Date]@row - TODAY()) <= 7), "Yellow", "Green")))))))

Bianca Barrow

Hello Leibel,

Thank-you for your reply/help.

I'm looking at this line itme and with the formula you provided line item is reflecting a "red" health of work. although due date is in August.

Can you fix this? Health of work should be reflecting as 'green'

Jeff Reisman

@Bianca Barrow @Leibel Shuchat Since you have so many criteria involved, it may be a good idea to add a helper column or two to break up some of the criteria evaluation into manageable chunks, and then using some other formulas to evaluate those results and determine your color ball.

You have no criteria in here that match "In Progress", 50% complete, End Date more than 7 days out, but the formula can't make it to that final "green" at the very end. It's only making it to 5False(6True6False), and that's where it's stopping because it has not matched any criteria and there's a completed False condition in 5False(6True6False), leaving the ball as red.

At 5True, it hits its first logical expression that's true: Status@row = "In progress". So it chooses the true condition, which is testing the % complete being 80-90% and Today being later than the End Date. Neither of those conditions are true, so it can't be green, it has to go on to the next IF, which tests % Complete less than 80% (true!) and End Date coming in the next 7 days (false!) so it can't be yellow, leaving the false condition of red.

1True =IF(Status@row = "Done", "Blue",

1False2True IF(Status@row = "Blocked", "Red",

2False3True IF(Status@row = "New", IF(TODAY() > [End Date]@row, "Red",

3False(4True4False) IF(([End Date]@row - TODAY()) <= 7, "Yellow", "Green")),

5True IF(Status@row = "In progress", IF(AND([% Complete]@row >= 0.8, [% Complete]@row <= 0.9, TODAY() > [End Date]@row), "Green",

5False(6True6False) IF(AND([% Complete]@row < 0.8, ([End Date]@row - TODAY()) <= 7), "Yellow", "Red")),

7True IF(Status@row = "Review", IF(TODAY() > [End Date]@row, "Red",

7False(8True8False) IF(AND([% Complete]@row < 0.9, ([End Date]@row - TODAY()) <= 7), "Yellow", "Green")))))))