Explain =IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green

toni.toni601

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

Kelly Moore

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 at-risk 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

toni.toni601

Here's my Schedule Delta (%) formula =IFERROR([Schedule Delta (Working Days)]@row / Duration@row, "")

Yes, looks like I need additional conditions

toni.toni601

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???

Kelly Moore

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