none
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
    Moderator
  • 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 Ismet Kocaman Thursday, October 12, 2017 12:32 PM typo
    Thursday, October 12, 2017 12:30 PM