tag:blogger.com,1999:blog-1971974841508744352.post5595820832462427068..comments2024-02-16T02:27:30.012-05:00Comments on Chatting about Excel and More: NESTED IF STATEMENTSexcel-divahttp://www.blogger.com/profile/13780428201816311547noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-1971974841508744352.post-23989342865467672812012-03-16T12:54:33.261-04:002012-03-16T12:54:33.261-04:00I think that your problem may be a simple one - it...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.<br />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. <br />I have a couple of blog entries on Match Index - here is one of them<br />http://excel-diva.blogspot.com/2010/09/2-way-lookup-using-index-match.htmlexcel-divahttps://www.blogger.com/profile/13780428201816311547noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-39729409625334138602012-03-12T17:01:59.154-04:002012-03-12T17:01:59.154-04:00I have MS office professional 2010. I need to do a...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?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-66141292339641251062011-07-24T23:05:24.781-04:002011-07-24T23:05:24.781-04:00Sorry- I am not totally clear on what you are aski...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.<br /> 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")))))))<br />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?Patriciahttp://cpaselfstudy.comnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-65288361232276189292011-07-23T08:26:07.546-04:002011-07-23T08:26:07.546-04:00As per excel 2007 it is 64 levels, I have used onl...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.<br /><br />Problem is:<br /><br />I am retrieving the first letter of each word in a cell. There are 3 formats<br /><br />example<br /><br />badri, narayana prasad<br />naveen, kumar<br />badri narayana, prasad<br /><br />=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)))," ")))<br /><br />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<br />but here it is taking 4 levels more than this nesting problem. where is 7 levels????<br /><br />example<br /><br />McDonald, Robert<br />StAnnes, Thomas<br />McDonald, Robert Thomas<br />StAnnes, Thomas Robert<br /> <br />=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))," "))))<br /><br />McDonald Robert, Thomas<br />StAnnes Thomas, Robert<br /><br />still this above format is pending now.<br /><br />In first looping more than 3 levels not allowing<br />In second looping more than 4 levels not allowing if use Excel 2007 but you told 64 levels how to resolve this problem.<br /><br />Now how to attach this 3 + 4 loops please give the solution.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-40706120045115770112011-04-22T13:04:23.611-04:002011-04-22T13:04:23.611-04:00I agree that many nested IF's can get pretty m...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. <br /><br />Thanks.<br /><br />C.W.Excel Statementhttp://www.oneclickcommissions.com/excel-statement.htmlnoreply@blogger.com