1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Help creating conditional formula

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

Thread Status:
Not open for further replies.
Advertisement
  1. hvb2811

    hvb2811 Thread Starter

    Joined:
    May 28, 2012
    Messages:
    3
    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. Pedro15

    Pedro15

    Joined:
    Oct 5, 2008
    Messages:
    386
    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. hvb2811

    hvb2811 Thread Starter

    Joined:
    May 28, 2012
    Messages:
    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. Pedro15

    Pedro15

    Joined:
    Oct 5, 2008
    Messages:
    386
    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. hvb2811

    hvb2811 Thread Starter

    Joined:
    May 28, 2012
    Messages:
    3
    That works for me! Thank you SO MUCH for your help!
     
  6. Pedro15

    Pedro15

    Joined:
    Oct 5, 2008
    Messages:
    386
    Good to hear. Thanks for the feedback

    Pedro
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1054949

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice