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

1. ## 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?

2. 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. Originally Posted by daddylonglegs
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. =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. Thankyou so much. Ill give it a go.

6. Ill have to play with it a bit it half works but is a lot better than what I had

7. hmm i thought i tested it for all possible conditions!!!
which ones don't work?

8. (A1=2,b2="NO",b4="NO"), answer should be A3 not A2 as a2 is "NO"

9. 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))

10. Thanks so much for your help Ive found a better way round it Data validation Hidden.

##### Users Browsing this Thread

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

#### 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