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
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.
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.
ReplyDeleteThanks.
C.W.
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.
ReplyDeleteProblem 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.
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.
ReplyDeleteHere 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?
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?
ReplyDeleteI 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.
ReplyDeleteIf 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