# Solved: Help creating conditional formula

Discussion in 'Business Applications' started by hvb2811, May 28, 2012.

Not open for further replies.
1. Despite using the information on the Microsoft website, I seem to be having problems with getting a correct conditional formula which results in a value other than true or false.

Basically in simple terms:
If value in Cell A1 is more than 19 and less than 25 I want the value 3 to go into A2.
(The formula is in cell A2)

Although I do not have a not true value to enter other than error, the formula below does not work - or even come up with there being an error.

Formula I am using is :
=IF(AND(M2>19,M2<25),"3","error")

I have tried also putting in the values 19 and 25 in other cells on the spreadsheet and using the cell detail in the formula but even this does not work.

I am using Excel 2010. Can anyone please tell me where I am going wrong or if there would be a better function to use? Many thanks in advance!!

2. You may have placed the incorrect reference number.

With 19 in cell A1 try this in A2
=IF(AND(A1>=19,A1<=25),"3","error")

On 2003 version it gives a answer of 3 in A2.

Pedro

3. Thank you Pedro!!

I was missing the = but I could have sworn I tried that variation. The reference to M2 is the actual location of the cell but I used A1 and A2 as an example to simplify things.

One further question now that formula works; is it possible to combine all the combinations below into one formula into the one cell to cover different results for different values? I know it should be possible in theory, but not sure on the placement of all the brackets &#8211; or if it is possible with the IF/AND function.

=IF(A1<=20, &#8220;2&#8221;, &#8220;error&#8221 =IF(AND(A1>=19,A1<=25),"3","error")
=IF(AND(A1>=24,A1<=31),&#8221;4&#8221;,&#8221;error&#8221 =IF(AND(A1>=30,A1<=36),&#8221;5&#8221;,&#8221;error&#8221 =IF(AND(A1>=35,A1<=41),&#8221;6&#8221;,&#8221;error&#8221 4. There was some overlapping in your requirements but this should work (until a shorter solution if given)

=IF(A1=19,"2",IF(AND(A1>=20,A1<=23),"3",IF(AND(A1>=24,A1<=29),"4",IF(AND(A1>=30,A1<=34),"5",IF(AND(A1>=35,A1<=41),"6","ERROR")))))

Pedro

5. That works for me! Thank you SO MUCH for your help!

6. Good to hear. Thanks for the feedback

Pedro