IF AND Function with < A Number > a Number

[leamercedes22]

I am having trouble with a formula and cant seem to figure out how to solve for.

I am trying to solve for a commission Plan. The Logic is if the Plan type is equal to A and the term of the contract is =<12 months than put Plan type A1, if equal to A and >12 months but <36 than apply plan A1A2, but if the term is over 36 months than you need A1A2A3, Otherwise ignore all that and just bring back what ever is in cell A3


Plan Type A3
Contract Term X3

So far I have this formula and its not working

=IF(AND(A3="A",X3<="12"),"A1",IF(AND(A3="A",X3>"12"),"A1A2",IF(AND(A3="A",X3>"36")"A3")))

 

[Joe4]

Welcome to the Board!

I haven't tried to analyze the logic in your formula yet, but I do see one potential problem that sticks out.

Note that when comparing numeric values, if your numbers are really entered as numbers and not text, do NOT use double-quotes! Those indicate Text, not Numeric entries.
So comparisons like this:

[COLOR=#333333]X3<="12"[/COLOR]

probably need to look like this:

[COLOR=#333333]X3<=12[/COLOR]

 

[leamercedes22]

Tried with and without Quotes and doesn't matter it still wont solve. Driving me bonkers. I really need to find a formula that will let me look at one cell than look at another and if its between 2 numbers than bring back a text and be able to do multiple nests of that logic

 

[jtakw]

Hi,

May be you can show some sample data and expected results?

 

[philwojo]

I think you need one tweak to your formula based upon what you wrote in your 1st post.

Try this:

[COLOR=#333333]=IF(AND(A3="A",X3<="12"),"A1",IF(AND(A3="A",X3>"12",[/COLOR][COLOR=#ff0000]X3<"36"[/COLOR][COLOR=#333333]),"A1A2",IF(AND(A3="A",X3>"36")"A3")))[/COLOR]

That being said I am not sure it will solve your issue.

Can you be more specific with what is happening now, what results do you get? Saying it won't solve is to generic to provide more feedback.

 

[leamercedes22]

Yup That's IT!!!!

the ending formula when applied to my actual sheet worked as

=IF(AND(AV3="A",X3<=12),"A1",IF(AND(AV3="A",X3>12,X3<=36),"A1A2",IF(AND(AV3="A",X3>36),"A1A2A3",AV3)))

Thank you.

 

[philwojo]

Glad you got it working.

 

[jtakw]

Yup That's IT!!!!

the ending formula when applied to my actual sheet worked as

=IF(AND(AV3="A",X3<=12),"A1",IF(AND(AV3="A",X3>12,X3<=36),"A1A2",IF(AND(AV3="A",X3>36),"A1A2A3",AV3)))

Thank you.

If that formula works for you, here's a shorter version that should work the same:

Book1
	E
1	0
Sheet15

Cell Formulas
Range	Formula
E1	=IF(AV3="A",IF(X3<=12,"A1",IF(X3<=36,"A1A2","A1A2A3")),AV3)