Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula too long
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
Long Long Long Nested If Function | Excel Discussion (Misc queries) | |||
Clearing cells takes long, long time | Excel Discussion (Misc queries) | |||
Formula too long | Excel Worksheet Functions | |||
long formula | Excel Worksheet Functions |