Microsoft Project Pro 2010 nested IIf statement help

Question

I am having trouble with my IIf statement below in Microsoft Projects Professional 2010. 

I want to automatically return a value using the formula and not have to manually input a value for each task.

Formula: IIf([Total Slack]<0,5,IIf([Total Slack]=0,4,IIf([Total Slack]<=5,3,IIf([Total Slack]<=10,2,1))))

Desired return:

Negative Slack -> 5

Zero days Slack -> 4

1-5 days Slack -> 3

6-10 days Slack -> 2

11+ days Slack -> 1

This formula will return a 1 for all slack greater than 0, so apparently it is not recognizing the 3rd and 4th condition.  I have tried the same formula in Excel and it works fine.

Actual return:

Negative Slack -> 5

Zero days Slack -> 4

1-5 days Slack -> 1

6-10 days Slack -> 1

11+ days Slack -> 1

Also, I have tried multiple formula variations that I could present logically, but with no change in result.  (A few examples below)

IIf([Total Slack]<0,5,IIf([Total Slack]=0,4,IIf([Total Slack]>0 AND [Total Slack]<=5,3,IIf([Total Slack]>5 AND [Total Slack]<=10,2,1))))

IIf([Total Slack]<0,5,IIf([Total Slack]<1,4,IIf([Total Slack]<=5,3,IIf([Total Slack]<=10,2,1))))

IIf([Total Slack]<0,5,IIf([Total Slack]<1,4,IIf([Total Slack]<6,3,IIf([Total Slack]<11,2,1))))

IIf([Total Slack]<0,5,IIf([Total Slack]=0,4,IIf([Total Slack]<6,3,IIf([Total Slack]<11,2,1))))

IIf([Total Slack]<0,5,IIf([Total Slack]=0,4,IIf([Total Slack]<=5,3,IIf([Total Slack]<=10,2,IIf([Total Slack]>10,1,"")))))

Can someone help with my logic or provide any suggestions that may work better?

Answer 1

John,

Yeah, I feel your frustration but we can help. Here's why your formula doesn't seem to work for Project but does for Excel. Project stores all time related data in minutes, so what you see as 5 days is stored internally as 2400 minutes (assuming you are using the default 8 hour work day). Negative and zero total slack values work fine because negative minutes are still negative and zero minutes is still zero. It's the positive values that need to be adjusted. So, try this formula:

IIf([Total Slack]<0,5,IIf([Total Slack]=0,4,IIf([Total Slack]<=2400,3,IIf([Total Slack]<=4800,2,1))))

If you are not using the default 8 hour day, you could also use the slightly more complex syntax:

IIf([Total Slack]<0,5,IIf([Total Slack]=0,4,IIf([Total Slack][Minutes Per Day]<=5,3,IIf([Total Slack][Minutes Per Day]<=10,2,1))))

Hope this helps.

John