Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
John Simons
 
Posts: n/a
Default 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   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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 arvil<attarkon.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   Report Post  
John Simons
 
Posts: n/a
Default

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(L14<0,L14+N14,@IF((@DATEDIF(C$4,E14,"m"))<1,(H 14-J14),@IF(F14<T$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$9<F$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(L14<0,0,@IF(F14<U$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+F14<13,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   Report Post  
JulieD
 
Posts: n/a
Default

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(L14<0,L14+N14,@IF((@DATEDIF(C$4,E14,"m"))<1,(H 14-J14),@IF(F14<T$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$9<F$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(L14<0,0,@IF(F14<U$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+F14<13,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.





  #7   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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 arvil<attarkon.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   Report Post  
John Simons
 
Posts: n/a
Default

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 arvil<attarkon.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   Report Post  
John Simons
 
Posts: n/a
Default

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(L14<0,L14+N14,@IF((@DATEDIF(C$4,E14,"m"))<1,(H 14-J14),@IF(F14<T$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$9<F$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(L14<0,0,@IF(F14<U$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+F14<13,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   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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 arvil<attarkon.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.








  #11   Report Post  
John Simons
 
Posts: n/a
Default

Arvi:
Yeah, I tried that, but the results were inconsistent. The original
equation worked beautifully, but when I break it apart, the results are
broken as well.

Compare the months 2 - 12 original with the following:

'Master' equation:
=IF($AM11<0,0,IF($G11<BP$9,0,IF(($G11-$H11)=BP$9,IF($G11-$H11<=$BM$1,0,0),IF(BP$8=$AM$8,($AM11/BP411),IF(BP$9<=$G11,IF(BP$8=$AO$8,$AO11/BP211,$BM$1),$BM$1)))))

Equation at "BP411":
=IF($G11$H11,IF($G11-$H11+$G1113,IF(BP$9<=$BM$1,MONTH($D11)-$BM$1,$H11),$H11-$G11+$BM$1),IF($G11<=$BM$1,$G11,IF(YEAR($D11)=BP$8 ,IF(BP$9=$BM$1,$BM$1,IF($H11-$G11=$BM$1,$BM$1,IF($BM$1=BP$9,$BM$1,(13-MONTH($D11))))),IF($G11-$BM$1=12,12,$BM$1))))

Equation at "BP211":
=IF($G11<=$BM$1,IF($H11-$G11$BM$1,$G11,$H11),IF(YEAR($D11)=BP$8,IF(BP$9<= $BM$1,$BM$1,IF($H11-$G11=$BM$1,$BM$1,13-MONTH($D11))),IF(BP$9<=$G11,IF(YEAR($D11)=BP$8,(13-MONTH($D11)),IF(YEAR($F11)=BP$8,MONTH($F11),IF($G1 1-$BM$112,12,IF($G11$BM$1,$BM$1,$G11-$BM$1)))),0)))

For some months and start and stop dates it works correctly, but for others
it does not.

Any suggestions?

John

"Arvi Laanemets" wrote:

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 arvil<attarkon.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.




  #12   Report Post  
John Simons
 
Posts: n/a
Default

Thanks for your efforts. I am going to bed as it is just after midnight
here. I will check back in the daylight hours!

John

"Arvi Laanemets" wrote:

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 arvil<attarkon.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.






  #13   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

Hi

OK. I worked through 1st formula (for month 1), and it's obvious you can't
use conventional methods here. Of-course I'm sure it's possible to simplify
your formulas (or even better - redesign them applying a bit more logic
:-)) ), but with current formula it looks like the only option left is using
named ranges.

An example based on your first formula:
(I assume all this is on sheet Sheet1 - otherwise edit formulas accordingly)

Select any cell on row 14 (Important!) - I write the formula for row 14 as
in your example.
Define 2 named ranges (from menu - Insert.Name.Define):

MonthNum1=IF(Sheet1!$F14<=Sheet1!$F$1,Sheet1!$F14, IF(YEAR(Sheet1!$D14)+1900=
Sheet1!$T$8,IF(Sheet1!$T$9<Sheet1!$F$1,Sheet1!$F$1 ,IF(Sheet1!$G14-Sheet1!$F1
4=Sheet1!$F$1,Sheet1!$F$1,(13-(MONTH(Sheet1!$D14))))),Sheet1!$F$1))

MonthNum2=IF(Sheet1!$H14-Sheet1!$I14-Sheet1!$K14=Sheet1!$N14,Sheet1!$F14,IF(
YEAR(Sheet1!$D14)+1900=Sheet1!$T$8,(13-(MONTH(Sheet1!$D14))),IF(YEAR(Sheet1!
$E14)+1900=Sheet1!$T$8,(MONTH(Sheet1!$E14)),MIN(12 ,Sheet1!$F14-Sheet1!$F$1))
))

Now the formula for Month 1 will be:
=IF(L14<0,L14+N14,IF((DATEDIF(C$4,E14,"m"))<1,H14-J14,OR(F14<T$9,F14-G14=T$
9)*IF((DATEDIF(C$4,E14,"m"))<1,H14-J14,IF(T$8=L$8,L14/MonthNum1,IF(AND(T$9<=
F14,T$8=N$8),N14/MonthNum2,0)))))

When copyed into another cell, the formula adjusts automatically.


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.



  #14   Report Post  
Harlan Grove
 
Posts: n/a
Default

John Simons wrote...
....
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(L14<0,L14+N14,@IF((@DATEDIF(C$4,E14,"m"))<1,( H14-J14),@IF(F14<T$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$9<F$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)))))))

....

First simplification: your initial test can be separated.

=(L14<0)*(L14+N14)+(L14=0)*IF(DATEDIF(C$4,E14,"m" )<1,...)

Next, there's this mishmash.

IF(DATEDIF(C$4,E14,"m")<1,
H14-J14,
IF(F14<T$9,
0,
IF(F14-G14=T$9,
0,
IF(DATEDIF(C$4,E14,"m")<1,
H14-J14,
IF(T$8=L$8,

This is an error. The first DATEDIF call is identical to the second
DATEDIF call, so the second one is NECESSARILY redundant. The only way
to get to the second DATEDIF call is if the first DATEDIF call returns
a number = 1, in which case the second one must NECESSARILY be false.
So the second 'IF(DATEDIF(...)<1,H14-J14,' can be deleted. Then merge
the two conditions that result in 0 and use their complement. So

IF(DATEDIF(C$4,E14,"m")<1,
H14-J14,
AND(F14=T$9,F14-G14<T$9)*IF(T$8=L$8,

The first denominator expression,

IF(F14<=F$1,
F14,
IF(YEAR(DATEVALUE(D14))+1900=T$8,
IF(T$9<F$1,
F$1,
IF(G14-F14=F$1,
F$1,
13-MONTH(D14)
)
),
F$1
)
)

evaluates to F1 in 3 different cases, but to F14 or 13-MONTH(D14) in
only one case, respectively. Simplify.

IF(F14<F$1,
F14,
IF((YEAR(--D14)=T$8)*((F$1<=T$9)+(G14-F14<F$1)),
13-MONTH(D14),
F$1
)
)

Note that Excel's YEAR function ALWAYS returns 4-digit years, so you
need to delete the '+1900' terms from ALL formulas. This is a real
PITA, but it's just one of those things Excel's 123 converter doesn't
catch. Also note that DATEVALUE is redundant in Excel - any string
representation of a number or a date in long or short date format per
Windows Regional Settings can be converted into numeric or date serial
value just by using it as an arithmetic operand. The '--' are two unary
minuses in sequence.

The final expression evaluates to 0 in 2 cases. Merge them and use
their complement. So replace

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
)

with

(T$9<=F14)*(T$8=N$8)*N14/IF(H14-I14-K14=N14,
F14,
IF(YEAR(--D14)=T$8,
13-MONTH(D14),
IF(YEAR(--E14)=T$8,
MONTH(E14),
MIN(F14-F$1,12)
)
)
)

That makes the entire equivalent Excel formula

=(L14<0)*(L14+N14)+(L14=0)*IF(DATEDIF(C$4,E14,"m" )<1,H14-J14,
AND(F14=T$9,F14-G14<T$9)*IF(T$8=L$8,L14/IF(F14<F$1,F14,
IF((YEAR(--D14)=T$8)*((F$1<=T$9)+(G14-F14<F$1)),13-MONTH(D14),F$1)),
(T$9<=F14)*(T$8=N$8)*N14/IF(H14-I14-K14=N14,F14,IF(YEAR(--D14)=T$8,
13-MONTH(D14),IF(YEAR(--E14)=T$8,MONTH(E14),MIN(F14-F$1,12))))))

Apply similar simplifications to your other formulas.

  #15   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

Hi

As Harlan pointed out (I did notice this yesterday at work, but missed later
at home), you can remove one condition from your formula:

=IF(L14<0,L14+N14,IF((DATEDIF(C$4,E14,"m"))<1,H14-J14,OR(F14<T$9,F14-G14=T$
9)*IF(T$8=L$8,L14/MonthNum1,IF(AND(T$9<=F14,T$8=N$8),N14/MonthNum2,0))))

(It looks like you have now one IF too much in MonthNum2 definition, to
merge them all to single formula again. Btw. Harlan simplified there too
something, did he?)

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Arvi Laanemets" wrote in message
...
Hi

OK. I worked through 1st formula (for month 1), and it's obvious you can't
use conventional methods here. Of-course I'm sure it's possible to

simplify
your formulas (or even better - redesign them applying a bit more logic
:-)) ), but with current formula it looks like the only option left is

using
named ranges.

An example based on your first formula:
(I assume all this is on sheet Sheet1 - otherwise edit formulas

accordingly)

Select any cell on row 14 (Important!) - I write the formula for row 14 as
in your example.
Define 2 named ranges (from menu - Insert.Name.Define):


MonthNum1=IF(Sheet1!$F14<=Sheet1!$F$1,Sheet1!$F14, IF(YEAR(Sheet1!$D14)+1900=

Sheet1!$T$8,IF(Sheet1!$T$9<Sheet1!$F$1,Sheet1!$F$1 ,IF(Sheet1!$G14-Sheet1!$F1
4=Sheet1!$F$1,Sheet1!$F$1,(13-(MONTH(Sheet1!$D14))))),Sheet1!$F$1))


MonthNum2=IF(Sheet1!$H14-Sheet1!$I14-Sheet1!$K14=Sheet1!$N14,Sheet1!$F14,IF(

YEAR(Sheet1!$D14)+1900=Sheet1!$T$8,(13-(MONTH(Sheet1!$D14))),IF(YEAR(Sheet1!

$E14)+1900=Sheet1!$T$8,(MONTH(Sheet1!$E14)),MIN(12 ,Sheet1!$F14-Sheet1!$F$1))
))

Now the formula for Month 1 will be:

=IF(L14<0,L14+N14,IF((DATEDIF(C$4,E14,"m"))<1,H14-J14,OR(F14<T$9,F14-G14=T$

9)*IF((DATEDIF(C$4,E14,"m"))<1,H14-J14,IF(T$8=L$8,L14/MonthNum1,IF(AND(T$9<=
F14,T$8=N$8),N14/MonthNum2,0)))))

When copyed into another cell, the formula adjusts automatically.


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.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Employing constant arrays to limit nested IF statements. Richard-44 Excel Worksheet Functions 2 January 6th 05 03:19 AM
7+ nested if statement? Turi Excel Worksheet Functions 3 December 20th 04 08:55 PM
Problem with data using IF and Nested IF statements possibly??? Ajay Excel Discussion (Misc queries) 2 December 9th 04 10:23 AM
nested statements Sherri New Users to Excel 6 December 3rd 04 08:04 PM
Do I need nested IF statements? Jaramya Excel Worksheet Functions 1 November 5th 04 10:10 PM


All times are GMT +1. The time now is 09:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"