+ Reply to Thread
Results 1 to 6 of 6

Nested AND function within IF function is only addressing logical1 but not logical2

  1. #1
    Registered User
    Join Date
    09-12-2012
    Location
    nashville, tn
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Nested AND function within IF function is only addressing logical1 but not logical2

    I have a spreadsheet where I've entered this equation

    =IF(X2,'Leader Cost'!G11,IF(AND($A2<>"",AND($A2<>"First",$C2<>"Last"),AND($A2<>CoLeaderFirstName,$C2<>CoLeaderLastName)),AdminFee,"")).

    Everything functions properly except the AND($A2<>"First",$C2<>"Last".

    Just to clarify what X2 in the formula references, it's another formula that states =AND($A2=CoLeaderFirstName,$C2=CoLeaderLastName).

    If there is a name in A2 and C2, there should be an AdminFee in the cell, if I type First in cell A2 and Last in cell C2 the cell should be blank according to the formula, but when I type First in cell A2 with anything typed in C2 the cell goes blank. It looks like it is not addressing logical2 in the AND function. I'm starting to think my understanding of the order Excel processes these arguments is incorrect, so any help would be great!

    BTW, First and Last were originally LeaderFirstName and CoLeaderLastName, but I changed it to help work through what is going wrong.
    Last edited by betic; 10-20-2012 at 09:17 AM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,610

    Re: Nested AND function within IF function is only addressing logical1 but not logical2

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    09-12-2012
    Location
    nashville, tn
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: Nested AND function within IF function is only addressing logical1 but not logical2

    Here, I created a new workbook with only the formula in it. It's definitely not the workbook that's the issue, it's the formula.
    Attached Files Attached Files

  4. #4
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Nested AND function within IF function is only addressing logical1 but not logical2

    Are you on about that AND formula in K2. Its FALSE as A2= name1 & B2= name2

  5. #5
    Registered User
    Join Date
    09-12-2012
    Location
    nashville, tn
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: Nested AND function within IF function is only addressing logical1 but not logical2

    It should resolve to the next AND formula then. If I change A2 to CoLeaderFirst and C2 to CoLeaderLast, the amount changes to $10 as is should. If I take that portion of the formula out, the other section still does not behave correctly.

  6. #6
    Registered User
    Join Date
    09-12-2012
    Location
    nashville, tn
    MS-Off Ver
    Excel for Mac 2011
    Posts
    10

    Re: Nested AND function within IF function is only addressing logical1 but not logical2

    Never mind, after looking at Kevin UK's reply...I went back and reworked the formula where in one cell is
    Please Login or Register  to view this content.
    ...Did the same for the CoLeaderFirstName and CoLeaderLastName statements. Now the formula just looks to see if these resolve to true or false and works perfectly. Thanks Kevin for pointing that out!

+ 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