ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Formula too long (https://www.excelbanter.com/excel-programming/303693-formula-too-long.html)

Michael McClellan
Formula too long
 
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(M1550000,M15<200000),50 000*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(M15200000,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,'Pro duct 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(M15500000,50000*LOOKUP(E1 5,'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!

gitcypher[_29_]
Formula too long
 
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

--
Message posted from http://www.ExcelForum.com

gitcypher[_30_]
Formula too long
 
Would you mind posting the spreadsheet?

-Gitcyphe

--
Message posted from http://www.ExcelForum.com

keepITcool
Formula too long
 


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!M1550000;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!M15200000;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!M15500000;
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!M1550000;ORD!M15<200000);
50000*VLOOKUP(ORD!E15;luTbl;3)+
(ORD!M15-50000)*VLOOKUP(ORD!E15;luTbl;4);
IF( AND(ORD!M15200000;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!M15500000;
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


Michael McClellan wrote:

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(M1550000,M15<200000),50 000*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(M15200000,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,'Pro duct 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(M15500000,50000*LOOKUP(E1 5,'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!

Myrna Larson
Formula too long
 
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(M1550000,M15<200000),
50000*VLOOKUP(E15,Table,3)+(Orders!M15-50000)*VLOOKUP(Orders!E15,Table,4),
IF(AND(M15200000,Orders!M15<500000),50000*VLOOKUP (E15,Table,3)+150000*VLOOKUP(Orders!E15,Table,4)
+(Orders!M15-200000)*VLOOKUP(Orders!E15,Table,5),IF(M15500000, 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(M1550000,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.

On Fri, 09 Jul 2004 10:09:31 -0700, Michael McClellan
wrote:

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,'Produc t Line
Lookup'!A$29:A$62,'Product Line
Lookup'!C$29:C$62),IF(AND(M1550000,M15<200000),5 0000*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(M15200000,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,'Pr oduct 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(M15500000,50000*LOOKUP(E 15,'Product Line
Lookup'!$A29:A62,'Product Line
Lookup'!C29:C62)+150000*LOOKUP(Orders!E15,'Produc t Line
Lookup'!A29:A62,'Product Line
Lookup'!D29:D62)+200000*LOOKUP(Orders!E15,'Produc t 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)


All times are GMT +1. The time now is 10:57 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com