# If (and/or) statement with three conditions in Excel

I am trying to get a result using "=if(and/or)" function. I have to come up with 1 result in two condition within three cell references. If its "MR" or "CAS":

In cell 1 I have the "Year"

In cell 2 I have the "Month"

In cell 3 I have the "Day"

• If a1 = 0
• and a2 <= 6
• and a3 = 0
• then a4 = "CAS" hence "MR"

Somehow, I managed to fix my previous formula with this "=IF(C3>=1,"MR",IF(AND(D3>=6,E3>=1),"MR","CAS"))". However, I received this result. Trial 3, maybe just a little work around and this sheet won't stress me out. hehe. Hope somebody could help. Thanks in advance!

Ideally. If the tenure is exactly 6 months or below the result should be "CAS" but if the tenure comes up 6 months and 1 day the result should be "MR".

Using @user218076 formula. @user218076

• [Don't use uppercase title.] | What do you expect and what do you get? Aug 17, 2017 at 7:54
• What error are you getting? I just tried this and it worked fine. Aug 17, 2017 at 8:06
• Can you quote a few sample input and your expected output? It seems that your formula worked as how I expected. Aug 17, 2017 at 8:11
• You might have input cells in question formatted as Text. In that case the formula may not evaluate to the intended results. Check your formatting. Try the same formula on a new blank worksheet. By default if numbers are entered in to a cell, Excel shall treat it as Numeric. Aug 17, 2017 at 8:32
• Is it possible the numbers are formatted as text? Aug 17, 2017 at 15:57

There's just a missing "otherwise" in @user218076 formula.
It's a bit easier to reverse the logic - since there are 2 scenarios where tenure is always MR, combine them with an OR, then there's an IF/conditional when Months = 6, then every other scenario is CAS:

``````If (Years > 0 OR Months > 6) then "MR" otherwise
(if (Months = 6 AND Days > 0) then "MR" otherwise "CAS")
``````

So the formula in A2 is

``````=IF(OR(B2>0,C2>6),"MR",IF(AND(C2=6,D2>0),"MR","CAS"))
``````

https://i.imgur.com/jNFZvqh.jpg

• This really solved the problem!! Thank you so much!! How funny as I realized myself, I've been working to solve this formula for months already! LOL Please keep your kindness on helping many others here as well!
– Rey
Sep 13, 2017 at 6:54

You have an error in your formula. `=if(and(a1=0,a2<=6,**a3=0**),"CAS","MR")` This would always return MR if day doesn't equal 0. Based on your description of what you are trying to achieve, you need a nested if statement.

``````"=IF(AND(A1=0, A2<6),"CAS",IF(A2=6, IF(A3=0,"CAS","MR")))"
``````

Please see this image. https://i.stack.imgur.com/uJfMC.jpg (sorry I cannot post images yet.)

Here is the updated formula to match the correct cell locations as per this screenshot.

``````=IF(AND(B2=0,C2<6),"CAS",IF(C2=6,IF(D2=0,"CAS","MR")))
``````
• Hi @user218076! Thank you so much for your response. However, this formula somehow still doesn't fixed the issue. When the month reached 7 months it returns a FALSE result.
– Rey
Aug 22, 2017 at 3:46