tag:blogger.com,1999:blog-1971974841508744352.post5595820832462427068..comments2017-03-15T04:40:17.163-04:00Comments on Chatting about Excel and More: NESTED IF STATEMENTSPatricia McCarthyhttps://plus.google.com/116680764554441338694noreply@blogger.comBlogger12125tag:blogger.com,1999:blog-1971974841508744352.post-81341970190985529342013-05-30T18:31:01.196-04:002013-05-30T18:31:01.196-04:00Wow- more than 64! My hat is off to you. I would s...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.htmMs Excelhttp://www.blogger.com/profile/13780428201816311547noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-53602093388991986862013-05-30T18:02:25.107-04:002013-05-30T18:02:25.107-04:00im working with more than 64 nested IF statements(...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?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-30610756707130926042013-01-14T22:25:01.185-05:002013-01-14T22:25:01.185-05:00In Cel A1, you want a formula such as this. =IF(Sh...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")))<br /><br /><br />If you send me your email, I can send you a file that answers your questions. You can just email me at patricia@cpaselfstudy.comMs Excelhttp://www.blogger.com/profile/13780428201816311547noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-6969449298737740332013-01-11T20:29:28.211-05:002013-01-11T20:29:28.211-05:00I have a workbook that has 4 sheets in it. The fi...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. <br /> <br />Main Sheet A B C D<br />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<br />2 33 22 66<br />3 44 33 77<br />4 11 44 88<br />5 55 11 99<br />6 66 22 11<br />7 0 0 0<br /><br />Sheet 2 A B<br />1 R1 22<br />2 33<br />3 44<br />4 R4 11<br />5 55<br />6 66<br /><br />Sheet 3 A B C<br />1 11<br />2 B2 22<br />3 33<br />4 44<br />5 B5 11<br />6 22<br /><br />Sheet 4 A B C D<br />1 55<br />2 66<br />3 C3 77<br />4 88<br />5 99<br />6 C6 11<br /><br /><br />Can anyone help meAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-17879911818035686772013-01-11T19:51:32.799-05:002013-01-11T19:51:32.799-05:00I have a workbook that has 4 sheets in it. The fi...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.<br /><br />Main Sheet A B C D<br />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<br />2 33 22 66<br />3 44 33 77<br />4 11 44 88<br />5 55 11 99<br />6 66 22 11<br /><br /><br />Sheet 2 A B<br />1 R1 22<br />2 33<br />3 44<br />4 R4 11<br />5 55<br />6 66<br /><br />Sheet 3 A B C<br />1 11<br />2 B2 22<br />3 33<br />4 44<br />5 B5 11<br />6 22<br /><br />Sheet 4 A B C D<br />1 55<br />2 66<br />3 C3 77<br />4 88<br />5 99<br />6 C6 11<br /> <br /><br />Can anyone help me?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-92222723080469433532012-10-26T12:21:34.620-04:002012-10-26T12:21:34.620-04:00If I understood you correctly an INDEX MATCH with ...If I understood you correctly an INDEX MATCH with the MIN might work better. I have provided an example below.<br />Column 1 Column 2<br />9 20<br />8 19<br />7 18<br />6 17<br />1 16<br />5 15<br />4 14<br />3 13<br />2 12<br /> <br /> 16<br /> <br /> =INDEX(C5:C13,MATCH(MIN(B5:B13),B5:B13,0))<br /><br />If this is not what you are looking for can you send me an example - use patricia@cpaselfstudy.comMs Excelhttp://www.blogger.com/profile/13780428201816311547noreply@blogger.comtag:blogger.com,1999:blog-1971974841508744352.post-76176037483119238492012-10-25T21:40:30.598-04:002012-10-25T21:40:30.598-04:00I am currently using a =min(if(....... ) statemen...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.Franknoreply@blogger.comtag: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.htmlMs Excelhttp://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