If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Nested IF statements
I am trying to convert an old Lotus 1-2-3 spreadsheet into Excel. I have
several cells that contain multiple nested IF statements. I understand from the documentation that there is a limitation in Excel that says that you can only have 7 levels of nested IF statements. I have a need to go as many as 18 levels of nested IF statements. Is there any way to cajole Excel into not choking on this nesting? There are so many things that Excel does better than Lotus 1-2-3, but this is one big negative for Excel. Thanks for any input you can provide. |
#3
|
|||
|
|||
Hi
Another way is to use CHOOSE function, which allows up yo 24 different choices - values or expressions. To use CHOOSE, you have to transform your choice conditions to choice order numbers, (1, 2, ... up to 24). You can do it p.e. using MATCH function, but sometimes simple mathematical operations will do. Still another way is to use VLOOKUP function with array as argument, or with lookup table somewhere on worksheet. With VLOOKUP you are limited to values as choices only. -- When sending mail, use address arvilattarkon.ee Arvi Laanemets "John Simons" John wrote in message ... I am trying to convert an old Lotus 1-2-3 spreadsheet into Excel. I have several cells that contain multiple nested IF statements. I understand from the documentation that there is a limitation in Excel that says that you can only have 7 levels of nested IF statements. I have a need to go as many as 18 levels of nested IF statements. Is there any way to cajole Excel into not choking on this nesting? There are so many things that Excel does better than Lotus 1-2-3, but this is one big negative for Excel. Thanks for any input you can provide. |
#4
|
|||
|
|||
I forgot to post the code for those who are willing to go back a decade or
so. The application is distributing a forecasted amount month by month (out 120 months) based on the start and end date of a project. The start and end dates can be in the past, present or future. Because of the various scenarios possible, we needed to compile four separate equations: month 1, months 2 - 12, months 13 - 24, and months 25+. The equation for months 25+ converted easily (only 6 nested IF statements!) so I did not include them here. I am aware that Excel does not have the DATEDIF function, but I can get around that easily (only applicable for the first month anyway). Here are the equations direct from Lotus 1-2-3: Month 1: @IF(L140,L14+N14,@IF((@DATEDIF(C$4,E14,"m"))1,(H 14-J14),@IF(F14T$9,0,@IF((F14-G14)=T$9,0,@IF((@DATEDIF(C$4,E14,"m"))1,(H14-J14),@IF(T$8=L$8,(L14/@IF(F14=F$1,+F14,@IF(@YEAR(@DATEVALUE(+D14))+1900 =T$8,@IF(T$9F$1,F$1,@IF(G14-F14=F$1,F$1,(13-(@MONTH(+D14))))),F$1))),@IF(T$9=F14,@IF(T$8=N$8, N14/@IF(+H14-I14-K14=N14,F14,@IF(@YEAR(@DATEVALUE(+D14))+1900=T$8,( 13-(@MONTH(+D14))),@IF(@YEAR(@DATEVALUE(+E14))+1900=T $8,(@MONTH(+E14)),@IF(F14-F$112,12,F14-F$1)))),0),0))))))) Months 2 - 12: @IF(L140,0,@IF(F14U$9,0,@IF((F14-G14)=U$9,@IF(F14-G14=F$1,0,0),@IF(U$8=L$8,(L14/@IF(F14G14,@IF(F14-G14+F1413,G14,G14-F14+F$1),@IF(F14=F$1,+F14,@IF(@YEAR(@DATEVALUE(+D 14))+1900=U$8,@IF(U$9=F$1,F$1,@IF(G14-F14=F$1,F$1,@IF(F$1=U$9,F$1,(13-(@MONTH(+D14)))))),F$1)))),@IF(U$9=F14,@IF(U$8=N$ 8,N14/@IF(+H14-I14-K14=N14,@IF(G14-F14F$1,F14,G14),@IF(@YEAR(@DATEVALUE(+D14))+1900= U$8,(13-(@MONTH(+D14))),@IF(@YEAR(@DATEVALUE(+E14))+1900=U $8,(@MONTH(+E14)),@IF(F14-F$112,12,F14-F$1)))),0),0))))) Months 13 - 24: @IF(($F14-$G14)=AF$9,0,@IF(AF$8=$L$8,($L14/@IF(@YEAR(@DATEVALUE(+$D14))+1900=AF$8,(13-(@MONTH(+$D14))),$F$1)),@IF(AF$9=$F14,@IF(AF$8=$N $8,($N14/@IF(@YEAR(@DATEVALUE(+$D14))+1900=AF$8,(13-(@MONTH(+$D14))),@IF(@YEAR(@DATEVALUE(+$E14))+1900 =AF$8,(@MONTH(+$E14)),@IF($F14-$F$112,12,$F14-$F$1)))),@IF(AF$8=$O$8,($O14/@IF(@YEAR(@DATEVALUE(+$D14))+1900=AF$8,(13-(@MONTH(+$D14))),@IF(@YEAR(@DATEVALUE(+$E14))+1900 =AF$8,(@MONTH(+$E14)),@IF($F14-$F$112,12,$F14-$F$1)))),$P14/($F14-$F$1-24))),0))) The variables are as follows: C$4 = Current date D = Start month E = Completion month F = Months to completion from current date F$1 = Months remaining in current year G = Project duration L = Projected amount for current year (N = cy+1, 0 = cy+2, etc) L$8 = Current year column$8 = Year of month of distribution column$9 = # of month of distribution Column 'T' is the first month of distribution so T$8 would be the current year and T$9 would be 1. Thanks for any help you can be. JS "John Simons" wrote: I am trying to convert an old Lotus 1-2-3 spreadsheet into Excel. I have several cells that contain multiple nested IF statements. I understand from the documentation that there is a limitation in Excel that says that you can only have 7 levels of nested IF statements. I have a need to go as many as 18 levels of nested IF statements. Is there any way to cajole Excel into not choking on this nesting? There are so many things that Excel does better than Lotus 1-2-3, but this is one big negative for Excel. Thanks for any input you can provide. |
#5
|
|||
|
|||
Hi John
still reading through the formulas, but wanted to let you know that Excel DOES have the Datedif function - it's just not documented - check out http://www.cpearson.com/excel/datedif.htm for details - seem to be giving you lots of web pages today Cheers JulieD "John Simons" wrote in message ... I forgot to post the code for those who are willing to go back a decade or so. The application is distributing a forecasted amount month by month (out 120 months) based on the start and end date of a project. The start and end dates can be in the past, present or future. Because of the various scenarios possible, we needed to compile four separate equations: month 1, months 2 - 12, months 13 - 24, and months 25+. The equation for months 25+ converted easily (only 6 nested IF statements!) so I did not include them here. I am aware that Excel does not have the DATEDIF function, but I can get around that easily (only applicable for the first month anyway). Here are the equations direct from Lotus 1-2-3: Month 1: @IF(L140,L14+N14,@IF((@DATEDIF(C$4,E14,"m"))1,(H 14-J14),@IF(F14T$9,0,@IF((F14-G14)=T$9,0,@IF((@DATEDIF(C$4,E14,"m"))1,(H14-J14),@IF(T$8=L$8,(L14/@IF(F14=F$1,+F14,@IF(@YEAR(@DATEVALUE(+D14))+1900 =T$8,@IF(T$9F$1,F$1,@IF(G14-F14=F$1,F$1,(13-(@MONTH(+D14))))),F$1))),@IF(T$9=F14,@IF(T$8=N$8, N14/@IF(+H14-I14-K14=N14,F14,@IF(@YEAR(@DATEVALUE(+D14))+1900=T$8,( 13-(@MONTH(+D14))),@IF(@YEAR(@DATEVALUE(+E14))+1900=T $8,(@MONTH(+E14)),@IF(F14-F$112,12,F14-F$1)))),0),0))))))) Months 2 - 12: @IF(L140,0,@IF(F14U$9,0,@IF((F14-G14)=U$9,@IF(F14-G14=F$1,0,0),@IF(U$8=L$8,(L14/@IF(F14G14,@IF(F14-G14+F1413,G14,G14-F14+F$1),@IF(F14=F$1,+F14,@IF(@YEAR(@DATEVALUE(+D 14))+1900=U$8,@IF(U$9=F$1,F$1,@IF(G14-F14=F$1,F$1,@IF(F$1=U$9,F$1,(13-(@MONTH(+D14)))))),F$1)))),@IF(U$9=F14,@IF(U$8=N$ 8,N14/@IF(+H14-I14-K14=N14,@IF(G14-F14F$1,F14,G14),@IF(@YEAR(@DATEVALUE(+D14))+1900= U$8,(13-(@MONTH(+D14))),@IF(@YEAR(@DATEVALUE(+E14))+1900=U $8,(@MONTH(+E14)),@IF(F14-F$112,12,F14-F$1)))),0),0))))) Months 13 - 24: @IF(($F14-$G14)=AF$9,0,@IF(AF$8=$L$8,($L14/@IF(@YEAR(@DATEVALUE(+$D14))+1900=AF$8,(13-(@MONTH(+$D14))),$F$1)),@IF(AF$9=$F14,@IF(AF$8=$N $8,($N14/@IF(@YEAR(@DATEVALUE(+$D14))+1900=AF$8,(13-(@MONTH(+$D14))),@IF(@YEAR(@DATEVALUE(+$E14))+1900 =AF$8,(@MONTH(+$E14)),@IF($F14-$F$112,12,$F14-$F$1)))),@IF(AF$8=$O$8,($O14/@IF(@YEAR(@DATEVALUE(+$D14))+1900=AF$8,(13-(@MONTH(+$D14))),@IF(@YEAR(@DATEVALUE(+$E14))+1900 =AF$8,(@MONTH(+$E14)),@IF($F14-$F$112,12,$F14-$F$1)))),$P14/($F14-$F$1-24))),0))) The variables are as follows: C$4 = Current date D = Start month E = Completion month F = Months to completion from current date F$1 = Months remaining in current year G = Project duration L = Projected amount for current year (N = cy+1, 0 = cy+2, etc) L$8 = Current year column$8 = Year of month of distribution column$9 = # of month of distribution Column 'T' is the first month of distribution so T$8 would be the current year and T$9 would be 1. Thanks for any help you can be. JS "John Simons" wrote: I am trying to convert an old Lotus 1-2-3 spreadsheet into Excel. I have several cells that contain multiple nested IF statements. I understand from the documentation that there is a limitation in Excel that says that you can only have 7 levels of nested IF statements. I have a need to go as many as 18 levels of nested IF statements. Is there any way to cajole Excel into not choking on this nesting? There are so many things that Excel does better than Lotus 1-2-3, but this is one big negative for Excel. Thanks for any input you can provide. |
#6
|
|||
|
|||
Julie:
I have tried splitting up the equation into parts, but the results are not consistent. See my later post to get the details of the original. John "JulieD" wrote: Hi John check out http://www.cpearson.com/excel/nested.htm it may provide a solution for you Cheers JulieD "John Simons" John wrote in message ... I am trying to convert an old Lotus 1-2-3 spreadsheet into Excel. I have several cells that contain multiple nested IF statements. I understand from the documentation that there is a limitation in Excel that says that you can only have 7 levels of nested IF statements. I have a need to go as many as 18 levels of nested IF statements. Is there any way to cajole Excel into not choking on this nesting? There are so many things that Excel does better than Lotus 1-2-3, but this is one big negative for Excel. Thanks for any input you can provide. |
#7
|
|||
|
|||
Hi again
Almost forgot another option. You can breake your nested IF() to several independent parts. Depending the result being a number or string =IF(condition1,result1,0)+IF(condition2,result2,0) +...+IF(conditionN,resultN ,0) or =IF(condition1,result1,"")&IF(condition2,result2," ")&...&IF(conditionN,resul tN,"") Only a single condition can be true, to make this solution to work. -- When sending mail, use address arvilattarkon.ee Arvi Laanemets "John Simons" John wrote in message ... I am trying to convert an old Lotus 1-2-3 spreadsheet into Excel. I have several cells that contain multiple nested IF statements. I understand from the documentation that there is a limitation in Excel that says that you can only have 7 levels of nested IF statements. I have a need to go as many as 18 levels of nested IF statements. Is there any way to cajole Excel into not choking on this nesting? There are so many things that Excel does better than Lotus 1-2-3, but this is one big negative for Excel. Thanks for any input you can provide. |
#8
|
|||
|
|||
Arvi:
I have posted the original 1-2-3 code in a separate post. Is it possible to nest the 'choose' function? Because I am spreading this out over 120 months, I think I would quickly exceed the 24 choices. If you could examine the original 1-2-3 code and give some suggestions, I would be most appreciative. John "Arvi Laanemets" wrote: Hi Another way is to use CHOOSE function, which allows up yo 24 different choices - values or expressions. To use CHOOSE, you have to transform your choice conditions to choice order numbers, (1, 2, ... up to 24). You can do it p.e. using MATCH function, but sometimes simple mathematical operations will do. Still another way is to use VLOOKUP function with array as argument, or with lookup table somewhere on worksheet. With VLOOKUP you are limited to values as choices only. -- When sending mail, use address arvilattarkon.ee Arvi Laanemets "John Simons" John wrote in message ... I am trying to convert an old Lotus 1-2-3 spreadsheet into Excel. I have several cells that contain multiple nested IF statements. I understand from the documentation that there is a limitation in Excel that says that you can only have 7 levels of nested IF statements. I have a need to go as many as 18 levels of nested IF statements. Is there any way to cajole Excel into not choking on this nesting? There are so many things that Excel does better than Lotus 1-2-3, but this is one big negative for Excel. Thanks for any input you can provide. |
#9
|
|||
|
|||
I liked the quote 'drunk cousin' in the link! I am curious if Excel supports
the DATEDIF function and the syntax is exactly as it appears in Lotus, why did it not convert when I brought the spreadsheet into Excel? Anyway, thanks for the info (one less formula to convert!) John "JulieD" wrote: Hi John still reading through the formulas, but wanted to let you know that Excel DOES have the Datedif function - it's just not documented - check out http://www.cpearson.com/excel/datedif.htm for details - seem to be giving you lots of web pages today Cheers JulieD "John Simons" wrote in message ... I forgot to post the code for those who are willing to go back a decade or so. The application is distributing a forecasted amount month by month (out 120 months) based on the start and end date of a project. The start and end dates can be in the past, present or future. Because of the various scenarios possible, we needed to compile four separate equations: month 1, months 2 - 12, months 13 - 24, and months 25+. The equation for months 25+ converted easily (only 6 nested IF statements!) so I did not include them here. I am aware that Excel does not have the DATEDIF function, but I can get around that easily (only applicable for the first month anyway). Here are the equations direct from Lotus 1-2-3: Month 1: @IF(L140,L14+N14,@IF((@DATEDIF(C$4,E14,"m"))1,(H 14-J14),@IF(F14T$9,0,@IF((F14-G14)=T$9,0,@IF((@DATEDIF(C$4,E14,"m"))1,(H14-J14),@IF(T$8=L$8,(L14/@IF(F14=F$1,+F14,@IF(@YEAR(@DATEVALUE(+D14))+1900 =T$8,@IF(T$9F$1,F$1,@IF(G14-F14=F$1,F$1,(13-(@MONTH(+D14))))),F$1))),@IF(T$9=F14,@IF(T$8=N$8, N14/@IF(+H14-I14-K14=N14,F14,@IF(@YEAR(@DATEVALUE(+D14))+1900=T$8,( 13-(@MONTH(+D14))),@IF(@YEAR(@DATEVALUE(+E14))+1900=T $8,(@MONTH(+E14)),@IF(F14-F$112,12,F14-F$1)))),0),0))))))) Months 2 - 12: @IF(L140,0,@IF(F14U$9,0,@IF((F14-G14)=U$9,@IF(F14-G14=F$1,0,0),@IF(U$8=L$8,(L14/@IF(F14G14,@IF(F14-G14+F1413,G14,G14-F14+F$1),@IF(F14=F$1,+F14,@IF(@YEAR(@DATEVALUE(+D 14))+1900=U$8,@IF(U$9=F$1,F$1,@IF(G14-F14=F$1,F$1,@IF(F$1=U$9,F$1,(13-(@MONTH(+D14)))))),F$1)))),@IF(U$9=F14,@IF(U$8=N$ 8,N14/@IF(+H14-I14-K14=N14,@IF(G14-F14F$1,F14,G14),@IF(@YEAR(@DATEVALUE(+D14))+1900= U$8,(13-(@MONTH(+D14))),@IF(@YEAR(@DATEVALUE(+E14))+1900=U $8,(@MONTH(+E14)),@IF(F14-F$112,12,F14-F$1)))),0),0))))) Months 13 - 24: @IF(($F14-$G14)=AF$9,0,@IF(AF$8=$L$8,($L14/@IF(@YEAR(@DATEVALUE(+$D14))+1900=AF$8,(13-(@MONTH(+$D14))),$F$1)),@IF(AF$9=$F14,@IF(AF$8=$N $8,($N14/@IF(@YEAR(@DATEVALUE(+$D14))+1900=AF$8,(13-(@MONTH(+$D14))),@IF(@YEAR(@DATEVALUE(+$E14))+1900 =AF$8,(@MONTH(+$E14)),@IF($F14-$F$112,12,$F14-$F$1)))),@IF(AF$8=$O$8,($O14/@IF(@YEAR(@DATEVALUE(+$D14))+1900=AF$8,(13-(@MONTH(+$D14))),@IF(@YEAR(@DATEVALUE(+$E14))+1900 =AF$8,(@MONTH(+$E14)),@IF($F14-$F$112,12,$F14-$F$1)))),$P14/($F14-$F$1-24))),0))) The variables are as follows: C$4 = Current date D = Start month E = Completion month F = Months to completion from current date F$1 = Months remaining in current year G = Project duration L = Projected amount for current year (N = cy+1, 0 = cy+2, etc) L$8 = Current year column$8 = Year of month of distribution column$9 = # of month of distribution Column 'T' is the first month of distribution so T$8 would be the current year and T$9 would be 1. Thanks for any help you can be. JS "John Simons" wrote: I am trying to convert an old Lotus 1-2-3 spreadsheet into Excel. I have several cells that contain multiple nested IF statements. I understand from the documentation that there is a limitation in Excel that says that you can only have 7 levels of nested IF statements. I have a need to go as many as 18 levels of nested IF statements. Is there any way to cajole Excel into not choking on this nesting? There are so many things that Excel does better than Lotus 1-2-3, but this is one big negative for Excel. Thanks for any input you can provide. |
#10
|
|||
|
|||
Hi
I have to leve at moment for some hours. When I return and see, that you didn't get a passing solution jet, I'll give it a look. Arvi Laanemets "John Simons" wrote in message ... Arvi: I have posted the original 1-2-3 code in a separate post. Is it possible to nest the 'choose' function? Because I am spreading this out over 120 months, I think I would quickly exceed the 24 choices. If you could examine the original 1-2-3 code and give some suggestions, I would be most appreciative. John "Arvi Laanemets" wrote: Hi Another way is to use CHOOSE function, which allows up yo 24 different choices - values or expressions. To use CHOOSE, you have to transform your choice conditions to choice order numbers, (1, 2, ... up to 24). You can do it p.e. using MATCH function, but sometimes simple mathematical operations will do. Still another way is to use VLOOKUP function with array as argument, or with lookup table somewhere on worksheet. With VLOOKUP you are limited to values as choices only. -- When sending mail, use address arvilattarkon.ee Arvi Laanemets "John Simons" John wrote in message ... I am trying to convert an old Lotus 1-2-3 spreadsheet into Excel. I have several cells that contain multiple nested IF statements. I understand from the documentation that there is a limitation in Excel that says that you can only have 7 levels of nested IF statements. I have a need to go as many as 18 levels of nested IF statements. Is there any way to cajole Excel into not choking on this nesting? There are so many things that Excel does better than Lotus 1-2-3, but this is one big negative for Excel. Thanks for any input you can provide. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Being Stupid - Help needed with nested IF statements | Andy B | Worksheet Functions | 1 | May 28th, 2004 02:06 PM |
multiple nested IF Statements | Jay | Worksheet Functions | 1 | December 2nd, 2003 03:40 PM |