Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2015
    Posts
    11
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Combining Multiple IF Statement with AND and OR

    Iím looking for a little help combining multiple IF statements. I donít have a lot of experience with this, so I created a table with each of my conditions to help. I can get a single conditions and the desired results to work; however, when I attempt to combine the statements, I cannot get my syntax correct. Excel will accept my formula, but it returns a #Value error in the cell. Iíve done some research, but since Excel is not showing a formula error, Iím have a hard time isolating what part(s) of the formula is wrong.

    Hereís what Iím trying to accomplish.

    Condition 1: If A1 is greater than today; E1 is blank

    Condition 2: If A1 equals to today AND B1, C1 and D1 (all 3) are blank, E1 is 10

    Condition 3: If A1 equals to less than today and B1, C1 or D1 (either of the 3) are NOT blank, E1 is 20; otherwise E1 is 30

    Hereís my formula:
    =IF(B31>TODAY(),""),IF((AND(B31=TODAY(),C31="",D31 ="",E31="",)),10),IF(OR(B30>=TODAY(),C30<>"",D30<> "",E30<>"",),20,30)

    IF.jpg

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    JAATR,

    I think this fixes the formula but I'm not sure what you are trying to accomplish:

    =IF(B31>TODAY(),"",IF(AND(B31=TODAY(),C31="",D31=" "),10,IF(OR(B30>=TODAY(),C30<>"",D30<>"",E30<>""),20,30)))

    The term in blue does not make sense based on your example as that column would not contain a date! Likewise, I deleted the term E31="" as that is the cell containing this formula!

    JAATR.PNG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Aug 2015
    Posts
    11
    Thanks
    6
    Thanked 0 Times in 0 Posts
    RetiredGeek

    Thanks for your help. Your formula worked perfectly.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Jaatr

    I’m have a hard time isolating what part(s) of the formula is wrong.
    ..put the cellpointer in the cell that has a complex formula. In the formula bar, use the mouse (or whatever) to highlight some part of the formula sections, press [F9] and it will calc that particular section. You can use the calc button in this way to check which parts of the formula give the expected results.

    zeddy

Posting Permissions

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