Forum Discussion

LesKing's avatar
LesKing
Copper Contributor
Jun 26, 2023

Problems with if/and/or type multiple/nested conditions.

I’m still having problems with if/and/or type multiple/nested conditions. Can anyone help please with the following. I need a single formula which meets the conditions below:

 

If J2 = “Mandatory” AND W2=”” then Y2 = “Needed”

If J2 = “Mandatory” AND W2 <>”” then Y2 = W2+1095

If J2 = “Ideally” AND W2=”” then Y2 = “Ideally”

If J2 = “Ideally” AND W2 <>”” then Y2 = W2+1095

If J2 = “Not Required” AND W2=”” then Y2 = “Not Required”

If J2 = “Not Required” AND W2 <>”” then Y2 = “Not Required”

I can’t get my head around getting this into a single formula. Can anyone help please.

excel

5 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jun 26, 2023

    LesKing 

    If literally

    =IF( (J2 = "Mandatory")*(W2=""), "Needed",
 IF( (J2 = "Mandatory")*(W2<>""), W2+1095,
 IF( (J2 = "Ideally")*(W2=""), "Ideally",
 IF( (J2 = "Ideally")*(W2<>""),  W2+1095,
 IF( (J2 = "Not Required")*(W2=""), "Not Required",
 IF( (J2 = "Not Required")*(W2=""), "Not Required")
)))))
    • LesKing's avatar
      LesKing
      Copper Contributor
      Jun 26, 2023
      Hi Sergei,
      Thank you so much, that works perfectly. I hadn't realised about using the * so I've still got a whole lot to learn!
      Thanks again, Les King
      • mtarler's avatar
        mtarler
        Silver Contributor
        Jun 26, 2023

        LesKing  alternatively this is also nearly literal but instead of separate IF conditions for ="" vs <>"", this uses a single IF

        =IF( J2 = "Mandatory", IF (W2="", "Needed", W2+1095),
 IF( J2 = "Ideally", IF(W2="", "Ideally", W2+1095),
 IF( J2 = "Not Required", IF(W2="", "Not Required", "Not Required")
 )))

         another option is SWITCH:

        =SWITCH(J2, "Mandatory", IF (W2="", "Needed", W2+1095),
            "Ideally", IF(W2="", "Ideally", W2+1095),
            "Not Required", IF(W2="", "Not Required", "Not Required"),
         "not found")

        so SWITCH is useful when doing multiple comparisons on the same cell/value.  The last output is if none of those 3 options are in J2. 

Resources