0

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

6
  • [Don't use uppercase title.] | What do you expect and what do you get?
    – user202729
    Aug 17, 2017 at 7:54
  • What error are you getting? I just tried this and it worked fine.
    – Stephen
    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.
    – Kenneth L
    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.
    – patkim
    Aug 17, 2017 at 8:32
  • Is it possible the numbers are formatted as text?
    – Chef1075
    Aug 17, 2017 at 15:57

2 Answers 2

Reset to default
0

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

1
  • 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
0

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")))
1
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.