# Nested Statement in MS Project

### Question

• I am trying to make an if and statement work in MS Project that I have working in Excel.

Formula in Excel (cell G4=duration)

=IF(AND(G4>=0,G4<=20),5,IF(AND(G4>20,G4<41),4,IF(AND(G4>40,G4<61),3,IF(AND(G4>60,G4<81),2,IF(G4>81,1,"invalid")))))

Formula in MS Project (only returning 5 and 0)

IIf([Duration]>=0,"5",IIf([Duration]<=20,"5",IIf([Duration]>20,"4",IIf([Duration]<41,"4",IIf([Duration]>40,"3",IIf([Duration]<61,"3",IIf([Duration]>60,"2",IIf([Duration]<81,"2",IIf([Duration]>81,"1","invalid")))))))))

Wednesday, October 11, 2017 9:55 PM

### All replies

• Hi,

there are several issues in your formula, e.g. with the first if: If duration >= 0 is quite true for everything, so you will get "5". You did not use the AND from Excel at all.

Another one is the usage of [Duration]. This field is repersenting the duration in minutes. So you need to use factor 480 (minutes/day) for comapring values.

Anyway, nested ifs are really bad to read. So I suggest to use switch. (Switch(condition1, result1, condition2, result2, ...) Switch will stop whenever a contition is true for the first time. So my suggestion for a field of type Number:

Switch([Duration]>=0 And [Duration]<=20*480;5;[Duration]>20*480 And [Duration]<=40*480;4;[Duration]>40*480 And [Duration]<=61*480;3;[Duration]>60*480 And [Duration]<=80*480;2;True;-20)

With the last part (True;-20), this will give you -20 if none of the previous contitions was met, using TRUE as condition for "all of the rest". If you are using a field of type text, you can use "invalid" instead of my -20.

Does that help?
Barbara

Thursday, October 12, 2017 5:56 AM
• As Barbara has mentioned, using the Switch function will make the formula easy to read and maintain.

I presume that the duration values entered represent minutes, otherwise Barbara has already explained how to adjust them.

This is how the Excel formula translates into inclusive ranges:
0 - 20 <-- 5  G4>=0,G4<=20
21 - 40 <-- 4 G4>20,G4<41
41 - 60 <-- 3 (G4>40,G4<61)
61 - 80 <-- 2 (G4>60,G4<81)
82 - above <-- 1  G4>81
Negative values are invalid.

You cannot enter negative durations in Project, so no need to test for it.

I did not test Barbara's formula but I believe it also works. This is my suggestion for the formula:

Switch ( [Duration] > 80, 1, [Duration] > 60, 2, [Duration] > 40, 3, [Duration] > 20, 4, True, 5 )

Here are the resulting ranges:

81 and above - 1
61 and 80    - 2
41 and 60    - 3
21 and 40    - 4
0 and 20    - 5

Adjust the ranges in anyway you want.

Ismet Kocaman | eBook on Formulas

• Edited by Thursday, October 12, 2017 12:32 PM typo
Thursday, October 12, 2017 12:30 PM