+ Reply to Thread
Results 1 to 6 of 6

Multiple if and Statments

  1. #1
    Registered User
    Join Date
    12-27-2012
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    19

    Multiple if and Statments

    Hi,

    For the life of me I can not work out why my formula won't work.

    =IF(AND(E7="F",N7>0),38,N7),IF(AND(E7="P",N7<38),N7,38),IF(E7="C",N7)

    Basically what I am trying to achieve is in cell E7 if it equals an F and Cell N7 is great than 0, then equal 38 or o. then if E7 equals a P, and N7 is =to or less than 38 be N7, or 38 and if E7 equals a C just be N7

    Any help would be greatly appreciated
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,703

    Re: Multiple if and Statments

    This is a complete IF statement in its own right; condition, result if true, result if false
    =IF(AND(E7="F",N7>0),38,N7),

    This is also a complete IF statement in its own right;
    IF(AND(E7="P",N7<38),N7,38),

    And this is a complete IF statement in its own right, although it would return False if the condition was not met;
    IF(E7="C",N7)


    For a Nested IF, you would need to replace the first false result by the second IF statement, and so on.


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    04-01-2009
    Location
    Irvine, CA
    MS-Off Ver
    Excel 2010
    Posts
    280

    Re: Multiple if and Statments

    Your IFs are not nested. It should be =IF(x, TRUE, IF(y, TRUE, IF(z, TRUE, FALSE)))

    In your case (I am a little unclear of what you want if all conditions fail):
    =IF(E7="C", N7, IF(AND(E7="F",N7>0),38,IF(AND(E7="P",N7<38),N7,"FAIL")))

    This checks for "C", then "F" & >0, then "P" & <38, if none then put FAIL.

  4. #4
    Registered User
    Join Date
    12-27-2012
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Multiple if and Statments

    Hey TMS,

    Thank you, I knew it was something so basic that I should have picked it up. I have been working on spreadhseets all week and have formuals coming out of my ears... Just needed that second pair of eyes to see where I was going wrong.

    Thank you very much for your help!

    Cheers

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,703

    Re: Multiple if and Statments

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  6. #6
    Registered User
    Join Date
    12-27-2012
    Location
    Melbourne
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Multiple if and Statments

    Hey Chance2,

    Thanks very much, that helped me also.

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1