# 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?  Register To Reply

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  Register To Reply

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 ""  Register To Reply

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)))))  Register To Reply

5. Thankyou so much. Ill give it a go.  Register To Reply

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

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

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

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))  Register To Reply

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