Excel - nesting the IF and AND function

I was wondering if anyone could help with the following. I have found one question and answer relating to my query (I think!!) but I cant break it down to do what I want.

    I have created a spreadsheet and used the IF & AND functions to do half of what I want. Basically if the answer is "no" in one column and one of four options in another (111,112,118,119) in another I want it to bring in the value of C2. This is what I have come up with        

    =IF(AND(E2= "No",F2=112),C2,0)

    This part works fine but I also need it to bring in value of C2 if "Yes" (E2) is selected AND the value of D2 is equal to F2 - this is what I have come up with

         =IF(AND(E2="No",F2=111),C2,0),IF(E2 ="Yes",D2=F2,C2).

    It doesn't work and I found the following answer by my excel skills just aren't up to pulling it apart to fit what I want. 

---

16

down vote

accepted

What you really need to do is put successive tests in the False argument. You're presently trying to separate each test by a comma, and that won't work.

Your first three tests can all be joined in one expression like:

        =IF(E2="In Play",IF(F2="Closed",3,IF(F2="suspended",2,IF(F2="Null",1))))

Remembering that each successive test needs to be the nested FALSE argument of the preceding test, you can do this:

    =IF(E2="In Play",IF(F2="Closed",3,IF(F2="suspended",2,IF(F2="Null",1))),IF(AND(E2="Pre-Play",F2="Null"),-1,IF(AND(E2="completed",F2="closed"),2,IF(AND(E2="suspended",F2="Null"),3,-2))))

shareedit

answered Apr 2 '13 at 0:37

David Zemens 40.7k94184

---

Any help would be greatly appreciated!!! :)

thanks in advance

James