RYG symbols and nest IF's

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")))

Find more posts tagged with

Manufacturing

IT & Operations

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…