Thursday, May 6, 2010

NESTED IF STATEMENTS

Nested IFs are one of those "must knows" and I couldn't believe that I had not written about it before.
A nested IF is an IF statement that has been placed within another IF. This is very handy if you want to test more than 2 conditions.

In Excel 2003, you can have a maximum of 7 levels of IF statements within an If statement although I have had people tell me they have created nested IFs with more than 7 levels using range names. In Excel 2007, you can have up to 64 levels.  Before we go any further, let me just say, that if you are anywhere over 7, you seriously need to learn about VLOOKUPs and INDEX MATCH functions. Honestly, no one wants to try to untangle or edit a 64 level IF statement.

Let's use this scenario: A bike company is offering some promotional tours and is trying to keep track of  them. There are 3 alternative statuses- the tour is underbooked, overbooked or full.

To create a nested IF with three conditions……………

Start the IF as you normally would and enter the test/condition and the True argument. In the example, I clicked in E14 and then clicked on the fx button to get the IF dialog box.

I typed =IF(C14 is greater than D14 as the Test and then clicked in the Value if True section and typed "underbooked"
(the quotations are because it is text)
Okay - I have taken care of one scenario- underbooked.













Then I clicked in the Value if False section (make sure you are in the False section!)
and  then clicked on the drop-down arrow in the Name box (above column A) and selected IF


The current dialog box collapses and is replaced with a brand new IF dialog box. (Don't worry -if you check the formula bar, you will see the earlier part of the formula still exists!)
I have 2 tests left since I have already taken care of the underbooked status.

 So, in the Logical Test, I tested C14=D14 and in the Value if true section typed "Full".
 "Overbooked" goes in the Value if False by default since that is the only option left.

If you click back on the fx button, Excel will bring back the entire nested IF so that you can see how it looks. Excel tests the first condition and if the test it met, it stops. If the test is not met, it continues to the next test and so on. 
In this particular example, the order of the tests did not matter, however if you are testing years or numbers, it may.
My rule of thumb is to start at the highest or largest number and work your way down.

12 comments:

  1. I agree that many nested IF's can get pretty messy. I was previously unaware of the "Index Match" function. I will have to look that up and see what type of solution it may offer.

    Thanks.

    C.W.

    ReplyDelete
  2. As per excel 2007 it is 64 levels, I have used only 3 levels of "ifs" in excel 2007, more than this if statement is not allowing it shows an error "more levels of nesting than are not allowed" how to resolve this problem.

    Problem is:

    I am retrieving the first letter of each word in a cell. There are 3 formats

    example

    badri, narayana prasad
    naveen, kumar
    badri narayana, prasad

    =IF(LEN(TRIM(A5))-LEN(SUBSTITUTE(A5," ",""))+1=2,CONCATENATE(MID(A5,1,1),", ",RIGHT(MID(A5,1,FIND(",",A5,1)+2))),IF(AND(RIGHT(MID(A5,1,FIND(" ",A5,1)-1))=",",LEN(TRIM(A5))-LEN(SUBSTITUTE(A5," ",""))+1=3),CONCATENATE(MID(A5,1,1),", ",RIGHT(MID(A5,1,FIND(",",A5,1)+2))," ",MID(A5,FIND(" ",A5,FIND(" ",A5)+1),2)),IF(AND(RIGHT(MID(A5,1,FIND(" ",A5,FIND(" ",A5)+1)-1))=",",LEN(TRIM(A5))-LEN(SUBSTITUTE(A5," ",""))+1=3),CONCATENATE(MID(A5,1,1)," ",RIGHT(MID(A5,1,FIND(" ",A5,1)+1)),", ",RIGHT(MID(A5,1,FIND(",",A5,1)+2)))," ")))

    i have done but now the client wants you see the name mcdonald and stanne means here first letter will not help instead of "mc" and "st" two letters must be retrieved. This also I have done
    but here it is taking 4 levels more than this nesting problem. where is 7 levels????

    example

    McDonald, Robert
    StAnnes, Thomas
    McDonald, Robert Thomas
    StAnnes, Thomas Robert

    =IF(AND(LEN(TRIM(A22))-LEN(SUBSTITUTE(A22," ",""))+1=2,LEFT(A22,2)="MC"),CONCATENATE(MID(A22,1,2),", ",RIGHT(MID(A22,1,FIND(",",A22,1)+2))),IF(AND(LEN(TRIM(A22))-LEN(SUBSTITUTE(A22," ",""))+1=2,LEFT(A22,2)="ST"),CONCATENATE(MID(A22,1,2),", ",RIGHT(MID(A22,1,FIND(",",A22,1)+2))),IF(AND(RIGHT(MID(A22,1,FIND(" ",A22,1)-1))=",",LEN(TRIM(A22))-LEN(SUBSTITUTE(A22," ",""))+1=3,LEFT(A22,2)="ST"),CONCATENATE(MID(A22,1,2),", ",RIGHT(MID(A22,1,FIND(",",A22,1)+2))," ",MID(A22,FIND(" ",A22,FIND(" ",A22)+1),2)),IF(AND(RIGHT(MID(A22,1,FIND(" ",A22,1)-1))=",",LEN(TRIM(A22))-LEN(SUBSTITUTE(A22," ",""))+1=3,LEFT(A22,2)="MC"),CONCATENATE(MID(A22,1,2),", ",RIGHT(MID(A22,1,FIND(",",A22,1)+2))," ",MID(A22,FIND(" ",A22,FIND(" ",A22)+1),2))," "))))

    McDonald Robert, Thomas
    StAnnes Thomas, Robert

    still this above format is pending now.

    In first looping more than 3 levels not allowing
    In second looping more than 4 levels not allowing if use Excel 2007 but you told 64 levels how to resolve this problem.

    Now how to attach this 3 + 4 loops please give the solution.

    ReplyDelete
  3. Sorry- I am not totally clear on what you are asking as the 2 examples seem the same aside from the number of characters you are retrieving.
    Here is a very simple example of 7 nested IFs. =IF(B2=1,1%,IF(B2=2,2%,IF(B2=3,3%,IF(B2=4,4%,IF(B2=5,5%,IF(B2=6,6%,IF(B2=7,7%,"This is the 8th test")))))))
    Personally, once I get above 3 or 4 IFs, I look for another solution. Can you not do Text to Columns on the name and break them apart by comma as the delimiter and then use an IF on them to retrieve the first 2 characters of each column or am I oversimplifying?

    ReplyDelete
  4. I have MS office professional 2010. I need to do a nested if with 10 levels. I am saving my workbook as .xls and as binary, but it will not allow me to do past the 7 levels. is there a setting somewhere i can change to allow the 10 levels, or how do you use the match index function?

    ReplyDelete
  5. I think that your problem may be a simple one - it may be due to the fact that you are saving the file as .xls. If you are using Office 10 teh default is .xlsx.
    If you are saving it down to .xls anything that the lower version doesn't recognize is de-activated so to speak. There is not setting that specifies the number of levels.
    I have a couple of blog entries on Match Index - here is one of them
    http://excel-diva.blogspot.com/2010/09/2-way-lookup-using-index-match.html

    ReplyDelete
  6. I am currently using a =min(if(....... ) statement. is it possible to make a =min(if(and(........) ? I want to check 1 column of numbers, say 1, and check the other column for 1's, then return the minimum value of the corresponding values of another column. I cant get the if-and to work.

    ReplyDelete
    Replies
    1. If I understood you correctly an INDEX MATCH with the MIN might work better. I have provided an example below.
      Column 1 Column 2
      9 20
      8 19
      7 18
      6 17
      1 16
      5 15
      4 14
      3 13
      2 12

      16

      =INDEX(C5:C13,MATCH(MIN(B5:B13),B5:B13,0))

      If this is not what you are looking for can you send me an example - use patricia@cpaselfstudy.com

      Delete
  7. I have a workbook that has 4 sheets in it. The first worksheet is the main sheet where all the formulas are. The second, third and fourth sheets are where the information is. I need a formula on the main sheet that will give me whatever is in cell A1 on sheet 2, 3 or 4. Only one of the sheets will have information in that cell. Here is an example of the sheets.

    Main Sheet A B C D
    1 Formula to go here that will go to sheets 2, 3, and 4 and see which sheet has something in cell A1 and put it here in this cell. 22 11 55
    2 33 22 66
    3 44 33 77
    4 11 44 88
    5 55 11 99
    6 66 22 11


    Sheet 2 A B
    1 R1 22
    2 33
    3 44
    4 R4 11
    5 55
    6 66

    Sheet 3 A B C
    1 11
    2 B2 22
    3 33
    4 44
    5 B5 11
    6 22

    Sheet 4 A B C D
    1 55
    2 66
    3 C3 77
    4 88
    5 99
    6 C6 11


    Can anyone help me?

    ReplyDelete
  8. I have a workbook that has 4 sheets in it. The first worksheet is the main sheet where all the formulas are. The second, third and fourth sheets are where the information is. I need a formula on the main sheet that will give me whatever is in cell A1 on sheet 2, 3 or 4. Only one of the sheets will have information in that cell. Here is an example of the sheets.

    Main Sheet A B C D
    1 =IF Formula goes here, to go to sheets 2, 3, and 4 and see which sheet has something in cell A1 and put it here in this cell. 22 11 55
    2 33 22 66
    3 44 33 77
    4 11 44 88
    5 55 11 99
    6 66 22 11
    7 0 0 0

    Sheet 2 A B
    1 R1 22
    2 33
    3 44
    4 R4 11
    5 55
    6 66

    Sheet 3 A B C
    1 11
    2 B2 22
    3 33
    4 44
    5 B5 11
    6 22

    Sheet 4 A B C D
    1 55
    2 66
    3 C3 77
    4 88
    5 99
    6 C6 11


    Can anyone help me

    ReplyDelete
  9. In Cel A1, you want a formula such as this. =IF(Sheet2!A1<>"",Sheet2!A1,IF(Sheet3!A1<>"",Sheet3!A1,IF(Sheet4!A1<>"",Sheet4!A1,"Error")))


    If you send me your email, I can send you a file that answers your questions. You can just email me at patricia@cpaselfstudy.com

    ReplyDelete
  10. im working with more than 64 nested IF statements(its a really large table), how can i exceed the 64 limit or how can i condense them to make it easier?

    ReplyDelete
  11. Wow- more than 64! My hat is off to you. I would suggest that you try to incorporate vlookups to get rid of some of the IF components. If you need to use them, you might consider defined names. Chip Pearson's post is a bit old as it discusses back when you could only nest 7 IFs however his comments still apply and will allow you to condense them down and make it more readable and easier to find errors. Check out his webpage on nested ifs http://www.cpearson.com/excel/nested.htm

    ReplyDelete


Ms. Excel- Resident Excel Geek