If Statements when comparing numbers

Jennifer Lewkowicz

Hi all,

I am working with a colleague to create formulas and I am stumped on this one. We've done a lot of logic on it and can only get two to work at any given time. He needs to make it so if the Pending Days Overall vs Days before Go Live is used to determine if a project is Ahead, On Track, At Risk, or Delayed.

If there are more pending days of work than Days before Go Live, then it's At Risk or Delayed. At Risk is 1-10 days behind. Delayed in 10+ days behind.

If there are less pending days of work than Days before Go Live, then it's Ahead or On Track. On Track is 0-10 ahead. Ahead is 10+ days head.

=IF([Pending Days Overall]@row > [Status : Days Available Before Go Live Date]@row, "DELAYED", IF([Pending Days Overall]@row + 10 < [Status : Days Available Before Go Live Date]@row, "AHEAD", IF([Pending Days Overall]@row - 10 > [Status : Days Available Before Go Live Date]@row, "At Risk", IF([Pending Days Overall]@row < [Status : Days Available Before Go Live Date]@row, "On Track", "COMPLETE"))))

With this version, Delayed and Ahead work. But we're trying to figure out how to get At Risk and On Track to work.

Thanks for all your help!

Mike TV

@Jennifer Lewkowicz

On the sample sheet I made, you can see 3 Statuses are working. "At Risk" has issues because it conflicts with "Delayed" and needs further criteria. The way "Delayed" is set up and being that it's the first IF statement, the formula is going to stop calculating on that first IF statement because "Pending Days Overall" is going to be greater than the other with a long column name. You're going to need to move the "At Risk" IF statement to the front of the whole formula.

=IF([Pending Days Overall]@row - 10 > [Status : Days Available Before Go Live Date]@row, "At Risk", IF([Pending Days Overall]@row > [Status : Days Available Before Go Live Date]@row, "DELAYED", IF([Pending Days Overall]@row + 10 < [Status : Days Available Before Go Live Date]@row, "AHEAD", IF([Pending Days Overall]@row < [Status : Days Available Before Go Live Date]@row, "On Track", "COMPLETE"))))

I'll be honest though, the "At Risk" conditions don't make sense. If there are 12 days of work left, why would you remove 10 days from them before comparing it against the days before the go live day?

Mike TV

@Jennifer Lewkowicz

On the sample sheet I made, you can see 3 Statuses are working. "At Risk" has issues because it conflicts with "Delayed" and needs further criteria. The way "Delayed" is set up and being that it's the first IF statement, the formula is going to stop calculating on that first IF statement because "Pending Days Overall" is going to be greater than the other with a long column name. You're going to need to move the "At Risk" IF statement to the front of the whole formula.

=IF([Pending Days Overall]@row - 10 > [Status : Days Available Before Go Live Date]@row, "At Risk", IF([Pending Days Overall]@row > [Status : Days Available Before Go Live Date]@row, "DELAYED", IF([Pending Days Overall]@row + 10 < [Status : Days Available Before Go Live Date]@row, "AHEAD", IF([Pending Days Overall]@row < [Status : Days Available Before Go Live Date]@row, "On Track", "COMPLETE"))))

I'll be honest though, the "At Risk" conditions don't make sense. If there are 12 days of work left, why would you remove 10 days from them before comparing it against the days before the go live day?