RYG symbols and nest IF's

Jtelles

I'm new to the community and frankly new to Smartsheets... I'm building some RYG indictors to give a quick visual indicator when a task is at "risk".. my nest IF statement is set to track a task against TODAY() - Finish... ie the closer the Finish date comes to Today's date, the higher the risk. My challenge is the Green symbol disappears when Complete hits 100%, regardless of the TODAY() - Finish number. How do I keep the Green symbol showing even if the Complete column reaches 100%?

 

=IF(Complete2 <> 1, IF(TODAY() - Finish2 > -2, "Red", IF(TODAY() - Finish2 > -4, "Yellow", "Green")))

Mike Wilday

I am not sure why, but when I pasted your formula in I was getting an error. I retyped the formula you had on my test sheet I got it to work great. Try copying and pasting what I have below. 

=IF(Complete2 <> 1, IF(TODAY() - Finish2 > -2, "Red", IF(TODAY() - Finish2 > -4, "Yellow", "Green")))

But, you could always add... IF(Complete2 = 1, "Green", at the front of the formula.

Jtelles

still dropping the GREEN symbol when the Complete % hits 100%... I changed the IF statement to this =IF(Complete2 = 1, "Green", IF(TODAY() - Finish2 > -2, "Red", IF(TODAY() - Finish2 > -4, "Yellow"))) and this seems to work better

 

Jtelles

I see a different challenge now with this statement =IF(Complete5 = 1, "Green", IF(TODAY() - Finish5 > -2, "Red", IF(TODAY() - Finish5 > -4, "Yellow")))

If the complete date is set out in the future (Say March 13th 2018)... How do I get the IF statement to show a green status even though its not 100% complete yet?

 

Mike Wilday

Try adding , "Green" at the end of that new one. 

=IF(Complete5 = 1, "Green", IF(TODAY() - Finish5 > -2, "Red", IF(TODAY() - Finish5 > -4, "Yellow", "Green")))

Jtelles

Mike

Thanks for the quick reply... that seems to work. My finish date is out in Mid March, the Complete % is still only at 50% and my RYG status is now showing Green!

=IF(Complete5 = 1, "Green", IF(TODAY() - Finish5 > -2, "Red", IF(TODAY() - Finish5 > -4, "Yellow", "Green")))

Jacques

Mike Wilday

No problem, glad I could help! 

Jtelles

Next challenge now that I"ve got a better understanding of applying IF statements to RYG symbols..

Measure the total # of days to delivery the task... If the task is less than 50% complete by 75% of the time allocated, show a yellow status. If the task is less than 75% complete by 85% of the time allocated, show a red status.

Is this doable?

 

Mike Wilday

Do you have a start and a finish date? 

Do you want workdays or calendar days? 

Jtelles

work timeline would be based on work days (ie mon to fri).

Lets assume a 20 day delivery timeline (4 work weeks) to keep it simple Feb 5th to March 2nd... ie 1 week being 25%, 2nd week 50% etc etc...

Mike Wilday

I am not a math whiz and don't have time to figure this out, but if you used this formula as a starting point you could probably calculate the percentage number and compare to a percentage point.

This formula is based on 15 being 75% of the time left and 18 being about 85% of the time left. But you can probably calculate the percentage by subtracting today() from the networkdays, dividing by the networkdays function again, and multiplying by 100 and comparing that to the percentage? I'll leave that in your hands to figure out, but here it is based on the math you suggested.

=IF(AND(networkdays(start24, finish24)<15, [% complete]24<=.5),"Yellow", IF(And(networkdays(start24, finish24)<18, [% complete]24<=.75), "Red"))

Jtelles

Mike

Thank you... let me play with the formula you provided. I'll post an update

 

Jacques