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.