Formula too long

  • Thread starter Michael McClellan
  • Start date
M

Michael McClellan

I have made a formula in excel that returns an error "Formula too long".
What are the limitations of excel? Is there a way around this? Here is
the formula.

=IF(D15="CPS",IF(M15<50000,M15*LOOKUP(E15,'Product Line
Lookup'!A$29:A$62,'Product Line
Lookup'!C$29:C$62),IF(AND(M15>50000,M15<200000),50000*LOOKUP(E15,'Produc
t Line Lookup'!A$29:A$62,'Product Line
Lookup'!C$29:C$62)+(Orders!M15-50000)*LOOKUP(Orders!E15,'Product Line
Lookup'!$A$29:$A$62,'Product Line
Lookup'!$D$29:$D$62),IF(AND(M15>200000,Orders!M15<500000),50000*LOOKUP(E
15,'Product Line Lookup'!$A$29:$A$62,'Product Line
Lookup'!$C$29:$C$62)+150000*LOOKUP(Orders!E15,'Product Line
Lookup'!$A$29:$A$62,'Product Line
Lookup'!$D$29:$D$62)+(Orders!M15-200000)*LOOKUP(Orders!E15,'Product Line
Lookup'!$A$29:$A$62,'Product Line
Lookup'!$E$29:$E$62),IF(M15>500000,50000*LOOKUP(E15,'Product Line
Lookup'!$A29:A62,'Product Line
Lookup'!C29:C62)+150000*LOOKUP(Orders!E15,'Product Line
Lookup'!A29:A62,'Product Line
Lookup'!D29:D62)+200000*LOOKUP(Orders!E15,'Product Line
Lookup'!A29:A62,'Product Line
Lookup'!E29:E62)+(Orders!M15-500000)*LOOKUP(Orders!E15,'Product Line
Lookup'!A29:A62,'Product Line Lookup'!F29:F62))))),N15*M15)



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
G

gitcypher

You can only have 7 IFs in one formula. I haven't really looked at i
yet, but you can have your seventh else portion go to anoter cell. Tha
other cell would continue your if statements.

-Gitcyphe
 
K

keepITcool

NOTE i'm using ; as list separator..

to make it shorter..
a. rename your sheets
b. change lookup to vlookup with column number...

you'll get this:
=IF(ORD!D15="CPS";
IF(ORD!M15<50000;
ORD!M15*VLOOKUP(ORD!E15;PL!A$29:F$62;3);
IF( AND(ORD!M15>50000;ORD!M15<200000);
50000*VLOOKUP(ORD!E15;PL!$A$29:$F$62;3)+
(ORD!M15-50000)*VLOOKUP(ORD!E15;PL!$A$29:$F$62;4);
IF( AND(ORD!M15>200000;ORD!M15<500000);
50000*VLOOKUP(ORD!E15;PL!$A$29:$F$62;3)+
150000*VLOOKUP(ORD!E15;PL!$A$29:$F$62;4)+
(ORD!M15-200000)*VLOOKUP(ORD!E15;PL!$A$29:$F$62;5);
IF( ORD!M15>500000;
50000*VLOOKUP(ORD!E15;PL!$A29:$F62;3)+
150000*VLOOKUP(ORD!E15;PL!A29:$F62;4)+
200000*VLOOKUP(ORD!E15;PL!A29:$F62;5)+
(ORD!M15-500000)*VLOOKUP(ORD!E15;PL!A29:$F62;6)))));
ORD!N15*ORD!M15)

c. use named ranges...

names define luTbl =PL!A$29:F$62

=IF(ORD!D15="CPS";
IF(ORD!M15<50000;
ORD!M15*VLOOKUP(ORD!E15;luTbl;3);
IF( AND(ORD!M15>50000;ORD!M15<200000);
50000*VLOOKUP(ORD!E15;luTbl;3)+
(ORD!M15-50000)*VLOOKUP(ORD!E15;luTbl;4);
IF( AND(ORD!M15>200000;ORD!M15<500000);
50000*VLOOKUP(ORD!E15;luTbl;3)+
150000*VLOOKUP(ORD!E15;luTbl;4)+
(ORD!M15-200000)*VLOOKUP(ORD!E15;luTbl;5);
IF( ORD!M15>500000;
50000*VLOOKUP(ORD!E15;luTbl;3)+
150000*VLOOKUP(ORD!E15;luTbl;4)+
200000*VLOOKUP(ORD!E15;luTbl;5)+
(ORD!M15-500000)*VLOOKUP(ORD!E15;luTbl;6)))));
ORD!N15*ORD!M15)

d. rip out the unneeded AND..

=IF(ORD!D15="CPS";
IF(ORD!M15<50000;
VLOOKUP(ORD!E15;luTbl;3)*ORD!M15;
IF(ORD!M15<150000;
VLOOKUP(ORD!E15;luTbl;3)*50000+
VLOOKUP(ORD!E15;luTbl;4)*(ORD!M15-50000);
IF(ORD!M15<200000;
VLOOKUP(ORD!E15;luTbl;3)*50000+
VLOOKUP(ORD!E15;luTbl;4)*150000+
VLOOKUP(ORD!E15;luTbl;5)*(ORD!M15-200000);
IF( ORD!M15<500000;
VLOOKUP(ORD!E15;luTbl;3)*50000+
VLOOKUP(ORD!E15;luTbl;4)*150000+
VLOOKUP(ORD!E15;luTbl;5)*200000+
VLOOKUP(ORD!E15;luTbl;6)*(ORD!M15-500000) ))));
ORD!N15*ORD!M15)



Finally we'll use arrays... and combine it with sumproduct ...

=IF(ORD!D15="CPS";
IF(ORD!M15<50000;
SUMPRODUCT(VLOOKUP(ORD!E15;lutbl;3);ORD!M15);
IF(ORD!M15<150000;
SUMPRODUCT(VLOOKUP(ORD!E15;lutbl;{3;4});
50000;(ORD!M15-50000));
IF(ORD!M15<200000;
SUMPRODUCT(VLOOKUP(ORD!E15;lutbl;{3;4;5});
50000;150000;(ORD!M15-200000));
IF( ORD!M15<500000;
SUMPRODUCT(VLOOKUP(ORD!E15;lutbl;{3;4;5;6});
50000;150000;200000;(ORD!M15-500000))
))));
ORD!N15*ORD!M15)


bit shorter now :)



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
M

Myrna Larson

As far as the length is concerned, assign names to the ranges containing the
table(s) (i.e. use Table as the name of the range 'Product Line
Lookup'!A$29:F$69) and use VLOOKUP instead of LOOKUP. The latter requires two
ranges, the former just a column number, i.e. VLOOKUP(E15,Table,3)

Table would refer to A2:E62 on sheet Product Line Lookup. The above formula
gets data from column C. When you want D, change the 3 to 4, etc.

=IF(D15="CPS",IF(M15<50000,M15*VLOOKUP(E15,Table,3),IF(AND(M15>50000,M15<200000),
50000*VLOOKUP(E15,Table,3)+(Orders!M15-50000)*VLOOKUP(Orders!E15,Table,4),
IF(AND(M15>200000,Orders!M15<500000),50000*VLOOKUP(E15,Table,3)+150000*VLOOKUP(Orders!E15,Table,4)
+(Orders!M15-200000)*VLOOKUP(Orders!E15,Table,5),IF(M15>500000,50000*VLOOKUP(E15,Table,3)
+150000*VLOOKUP(Orders!E15,Table,4)+200000*VLOOKUP(Orders!E15,Table,5)
+(Orders!M15-500000)*VLOOKUP(Orders!E15,Table,6))))),N15*M15)

(I may have the parentheses messed up in the above...)

If you have a problem with nested IF statements, you might get some help by
creating another lookup table that determines the multiplier, i.e. instead of
IF(AND(M15>50000,M15<200000),50000* .... to something like
VLOOKUP(M15,NewTable)*VLOOKUP(E15... which would return the number 50000.

But looking at your data, I wonder if what you need is a table that is
structured like our income tax tables, with the income amount in column 1, a
base dollar amount in column 2, and a multipler in column 3.
 
Top