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