Explain =IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green
Good evening all,
Can you explain what this formula is actually doing? This is the standard formula that is in the project plan in the Smartsheet PMO template. I think it should change "red" if my task is behind schedule. None of my late tasks are turning red, the project plan remains green for everything :(
Please help me understand what this formula actually means?
=IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))
Answers

Hey @toni.toni601
This formula is looking at your column [Schedule Delta (%)] and changing colors based on the percentages listed above. What is the formula in your [Schedule Delta (%)] column?
In words, the above formula says IF [Schedule Delta (%)] is greater that 10%, turn red. IF [Schedule Delta (%)] is greater than zero, Yellow. If neither of these two conditions are true, then anything else is green.
If you need additional conditions added to the above to help you escalate atrisk projects, the community can help you add to the above formula. All we need are the specific conditions (ex. Due Date has passed and %Complete is less than 100% = Red. Due Date is in 3 days and %Complete is 75% or less = Yellow. Etc.)
Kelly

Here's my Schedule Delta (%) formula =IFERROR([Schedule Delta (Working Days)]@row / Duration@row, "")
Yes, looks like I need additional conditions

Looks like this formula is calculating against the Target data. I'm looking for a formula that will calculate the work based on my status column. If the task is not completed and the end date has passed, the health status should turn red. Any ideas???

Hey @toni.toni601
We can build the formula to be based on practically anything you like. That's a good start, but what are the conditions for the other colors. Are you blending the first conditions with the one you just listed? Can you list out all conditions that would indicate Red? Yellow? Green? When you say you want it based on your Status column, what are the Status choices you are using?
Here's a start:
=IF(Status@row="Completed", "Green", IF(AND(Status@row<>"Completed", TODAY()>[End Date]@row), "Red", "Yellow"))
Kelly
Help Article Resources
Categories
Check out the Formula Handbook template!