Thread: 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?

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

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

=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(A 1=3,A2,CHOOSE(A1,2,3,1)))))

Thankyou so much. Ill give it a go.

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

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

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

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