+ Reply to Thread
Results 1 to 10 of 10

3 Way If formula & Advice needed on what book to buy

  1. #1
    Registered User
    Join Date
    10-12-2008
    Location
    Port Pirie
    Posts
    53

    3 Way If formula & Advice needed on what book to buy

    Im need to refine my formula for the attached sheet. The cell that requires the formula is e2. At the moment the cell returns False except when b4 says "no" I need it to return the a cell each time

    I have 2 excel books (excel 2007 for dummies and excel vba but I still cant find any help on formulas such as these) is there a book on just formulas or do I just learn from experience?
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    So what exactly do you want to do? You say you need to "return the a cell each time" but your formula is specifically looking for two conditions, when you use AND then both conditions must be true, e.g. for the first AND

    =IF(AND(D2=A2,B2="No"),A3.....

    If both conditions are true, i.e. D2=A2 and B2="No" then the formula will return the value of A3....otherwise the FALSE condition of the IF (the rest of the formula) is evaluated

  3. #3
    Registered User
    Join Date
    10-12-2008
    Location
    Port Pirie
    Posts
    53
    Quote Originally Posted by daddylonglegs View Post
    So what exactly do you want to do? You say you need to "return the a cell each time" but your formula is specifically looking for two conditions, when you use AND then both conditions must be true, e.g. for the first AND

    =IF(AND(D2=A2,B2="No"),A3.....

    If both conditions are true, i.e. D2=A2 and B2="No" then the formula will return the value of A3....otherwise the FALSE condition of the IF (the rest of the formula) is evaluated
    Okay so Im using the wrong formula, thankyou could you sugest what formula I should be using as I have no clue.

    The series is 1,2,3.
    If A1=(1) then E2=A3(2),
    If A1=(2) then E2=A4(3)
    If A1=(3) then E2=A2(1)

    If the next cell across b2:b4 reads NO then I need it to choose the next number in the series

    If A1=(1) then e2=A3(2), unless B3=NO then in this case it choose the next cell which would be A4, and then same if A4 also equals no.

    If all B2:b4=No then ""

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    =IF(AND(B2="no",B3="no",B4="no"),"",IF(AND(B2="no",B3="no"),A2,IF(AND(B2="no",OR(A1=1,A1=2)),A4,IF(A1=3,A2,CHOOSE(A1,2,3,1)))))

  5. #5
    Registered User
    Join Date
    10-12-2008
    Location
    Port Pirie
    Posts
    53
    Thankyou so much. Ill give it a go.

  6. #6
    Registered User
    Join Date
    10-12-2008
    Location
    Port Pirie
    Posts
    53
    Ill have to play with it a bit it half works but is a lot better than what I had

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    hmm i thought i tested it for all possible conditions!!!
    which ones don't work?

  8. #8
    Registered User
    Join Date
    10-12-2008
    Location
    Port Pirie
    Posts
    53
    (A1=2,b2="NO",b4="NO"), answer should be A3 not A2 as a2 is "NO"

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    i must have copied the wrong one from when i was working it out it out! i'll work it out again!
    it always goes down though to next value never back up? coz if it does if b2=no and b4=no then the answer should be blank as it cant work up the list.
    what would you expect if b4=no a1=2 ? (If A1=(2) then E2=A4(3))
    Last edited by martindwilson; 12-27-2008 at 07:07 PM.

  10. #10
    Registered User
    Join Date
    10-12-2008
    Location
    Port Pirie
    Posts
    53
    Thanks so much for your help Ive found a better way round it Data validation Hidden.

+ 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