Complicated Excel Formula: HELP!

  • Thread starter Thread starter JeffJ
  • Start date Start date
J

JeffJ

Using Excel 2000, I have two worksheets in the same workbook.

I'm going to hide and protect the second worksheet (which I have named
"FORMULAS"), so that only the first worksheet will be visible (which I
have named "SALESFORM"). The "FORMULAS" sheet will contain the IF
function formulas in it's cells, but they will be referencing the
matching cells on the "SALESFORM" sheet.
The purpose of this is so people can type in data on the "SALESFORM"
sheet, and not have to worry about accidently deleting the formulas
(since they will be on the other sheet, which is hidden and protected).
In order to do this, I type in SALESFORM! before each of the cell
references, so that it refers/points to the cells on the SALESFORM
sheet, not on the FORMULAS sheet. Interestingly, after I save it, it
always replaces the word SALESFORM! with the name of the workbook.

(All this has worked fine except for one particular formula, which is
too long. I have similar formulas elsewhere, and they are working
fine, but they are much shorter.)

One cell I have renamed "MODEL". When you enter a Model number into
this cell, it will bring up a description and a base price.
I have 3 other cells, named "ZONE_I", "ZONE_II" and "ZONE_III",
respectively. When you place and "X" in one of these cells, it will
bring up a monetary amount to either add or subtract to or from that
base price, depending on where you place the "X".

If I place my IF function formula in one cell, Excel says my formula is
too long. Therefore, I'm trying to split the formula into 2, 3, or
maybe even 4 cells. I've split it into 3 separate formulas, but the
1st and 3rd formula returns FALSE (I want it to be blank unless
something is typed into the MODEL cell and an "X" is placed in either
the ZONE_I, ZONE_II or ZONE_III cells).

First, just to give you an idea, here is an example of one of the
formulas that is working correctly (notice that it replaced every
occurance of SALESFORM! with the name of the workbook, 'LEXINGTON_Sept
15 Pricing.xls'!.....This is not a problem, but I'm just curious as to
why it does this):

=IF(AND('LEXINGTON_Sept 15 Pricing.xls'!ZONE_I="X",'LEXINGTON_Sept 15
Pricing.xls'!MODEL>0),-900,IF(AND('LEXINGTON_Sept 15
Pricing.xls'!ZONE_II="X",'LEXINGTON_Sept 15
Pricing.xls'!MODEL>0),"INCL",IF('LEXINGTON_Sept 15
Pricing.xls'!ZONE_III="X",1040," ")))


Now, here is the formula I'm having a problem with:

ORIGINAL FORMULA (EXCEL SAYS THIS IS TOO LONG):

=IF(AND(ZONE_I="X",MODEL>0),IF(OR(MODEL="2465CT",MODEL="2466CT",MODEL="2467CT",MODEL="2458CT",MODEL="2465CTH",MODEL="2466CTH",MODEL="2463CTH",MODEL="2458CTH",MODEL="2464CTH",MODEL="2550CT",MODEL="2567CT",MODEL="2463CTP",MODEL="2458CTP",MODEL="2464CTP",MODEL="2522CT",MODEL="2530CT"),-850,IF(OR(MODEL="2525CT",MODEL="2526CT",MODEL="2527CT",MODEL="2528CT",MODEL="4301CT",MODEL="4302CT"),-900,IF(OR(MODEL="2553CT",MODEL="2554CT",MODEL="2560CT",MODEL="2561CT",MODEL="2523CT",MODEL="2531CT"),-950))),IF(ZONE_II="X","INCL",IF(AND(ZONE_III="X",MODEL>0),IF(OR(MODEL="2465CT",MODEL="2466CT",MODEL="2467CT",MODEL="2458CT",MODEL="2465CTH",MODEL="2466CTH",MODEL="2463CTH",MODEL="2458CTH",MODEL="2464CTH",MODEL="2567CT",MODEL="2463CTP",MODEL="2458CTP",MODEL="2464CTP",MODEL="2522CT",MODEL="2530CT"),845,IF(OR(MODEL="2550CT",MODEL="2553CT",MODEL="2554CT",MODEL="2560CT",MODEL="2561CT",MODEL="2523CT",MODEL="2531CT",MODEL="4301CT",MODEL="4302CT"),1040,IF(OR(MODEL="2525CT",MODEL="2526CT",MODEL="2527CT",MODEL="2528CT"),995))),"
")))


FORMULA SPLIT INTO 3 PARTS:


PART I (first cell): This returns FALSE; I want it to be blank unless
an "X" is placed in Zone I.

=IF(AND(SALESFORM!ZONE_I="X",SALESFORM!MODEL>0),IF(OR(SALESFORM!MODEL="2465CT",SALESFORM!MODEL="2466CT",SALESFORM!MODEL="2467CT",SALESFORM!MODEL="2458CT",SALESFORM!MODEL="2465CTH",SALESFORM!MODEL="2466CTH",SALESFORM!MODEL="2463CTH",SALESFORM!MODEL="2458CTH",SALESFORM!MODEL="2464CTH",SALESFORM!MODEL="2550CT",SALESFORM!MODEL="2567CT",SALESFORM!MODEL="2463CTP",SALESFORM!MODEL="2458CTP",SALESFORM!MODEL="2464CTP",SALESFORM!MODEL="2522CT",SALESFORM!MODEL="2530CT"),"-850",IF(OR(SALESFORM!MODEL="2525CT",SALESFORM!MODEL="2526CT",SALESFORM!MODEL="2527CT",SALESFORM!MODEL="2528CT",SALESFORM!MODEL="4301CT",SALESFORM!MODEL="4302CT"),"-900",IF(OR(SALESFORM!MODEL="2553CT",SALESFORM!MODEL="2554CT",SALESFORM!MODEL="2560CT",SALESFORM!MODEL="2561CT",SALESFORM!MODEL="2523CT",SALESFORM!MODEL="2531CT"),"-950","
"))))


PART II (second cell): This one seems fine, possibly because it is not
too long. I'm wondering if the other two formulas are simply too
long.

=IF(AND(SALESFORM!ZONE_II="X",SALESFORM!MODEL>0),"INCL"," ")


PART III(third cell): This also returns FALSE; I want it to be blank
unless an "X" is placed in Zone III.

=IF(AND(SALESFORM!ZONE_III="X",SALESFORM!MODEL>0),IF(OR(SALESFORM!MODEL="2465CT",SALESFORM!MODEL="2466CT",SALESFORM!MODEL="2467CT",SALESFORM!MODEL="2458CT",SALESFORM!MODEL="2465CTH",SALESFORM!MODEL="2466CTH",SALESFORM!MODEL="2463CTH",SALESFORM!MODEL="2458CTH",SALESFORM!MODEL="2464CTH",SALESFORM!MODEL="2567CT",SALESFORM!MODEL="2463CTP",SALESFORM!MODEL="2458CTP",SALESFORM!MODEL="2464CTP",SALESFORM!MODEL="2522CT",SALESFORM!MODEL="2530CT"),"845",IF(OR(SALESFORM!MODEL="2550CT",SALESFORM!MODEL="2553CT",SALESFORM!MODEL="2554CT",SALESFORM!MODEL="2560CT",SALESFORM!MODEL="2561CT",SALESFORM!MODEL="2523CT",SALESFORM!MODEL="2531CT",SALESFORM!MODEL="4301CT",SALESFORM!MODEL="4302CT"),"1040",IF(OR(SALESFORM!MODEL="2525CT",SALESFORM!MODEL="2526CT",SALESFORM!MODEL="2527CT",SALESFORM!MODEL="2528CT"),"995",""))))
 
Jeff
Instead of your original formula try:

=IF(AND(ZONE_I="X",MODEL>0),IF(OR(MODEL={"2465CT","2466CT","2467CT",
"2458CT","2465CTH","2466CTH","2463CTH","2458CTH","2464CTH","2550CT",
"2567CT","2463CTP","2458CTP","2464CTP","2522CT","2530CT"}),-850,
IF(OR(MODEL={"2525CT","2526CT","2527CT","2528CT","4301CT",
"4302CT"}),-900,IF(OR(MODEL={"2553CT","2554CT",
"2560CT","2561CT","2523CT","2531CT"}),-950))),IF(ZONE_II="X","INCL",
IF(AND(ZONE_III="X",MODEL>0),IF(OR(MODEL={"2465CT","2466CT",
"2467CT","2458CT","2465CTH","2466CTH","2463CTH","2458CTH","2464CTH",
"2567CT","2463CTP","2458CTP","2464CTP","2522CT","2530CT"}),845,
IF(OR(MODEL={"2550CT","2553CT","2554CT","2560CT","2561CT","2523CT",
"2531CT","4301CT","4302CT"}),1040,
IF(OR(MODEL={"2525CT","2526CT","2527CT","2528CT"}),995))),"")))


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

JeffJ said:
Using Excel 2000, I have two worksheets in the same workbook.

I'm going to hide and protect the second worksheet (which I have named
"FORMULAS"), so that only the first worksheet will be visible (which I
have named "SALESFORM"). The "FORMULAS" sheet will contain the IF
function formulas in it's cells, but they will be referencing the
matching cells on the "SALESFORM" sheet.
The purpose of this is so people can type in data on the "SALESFORM"
sheet, and not have to worry about accidently deleting the formulas
(since they will be on the other sheet, which is hidden and protected).
In order to do this, I type in SALESFORM! before each of the cell
references, so that it refers/points to the cells on the SALESFORM
sheet, not on the FORMULAS sheet. Interestingly, after I save it, it
always replaces the word SALESFORM! with the name of the workbook.

(All this has worked fine except for one particular formula, which is
too long. I have similar formulas elsewhere, and they are working
fine, but they are much shorter.)

One cell I have renamed "MODEL". When you enter a Model number into
this cell, it will bring up a description and a base price.
I have 3 other cells, named "ZONE_I", "ZONE_II" and "ZONE_III",
respectively. When you place and "X" in one of these cells, it will
bring up a monetary amount to either add or subtract to or from that
base price, depending on where you place the "X".

If I place my IF function formula in one cell, Excel says my formula is
too long. Therefore, I'm trying to split the formula into 2, 3, or
maybe even 4 cells. I've split it into 3 separate formulas, but the
1st and 3rd formula returns FALSE (I want it to be blank unless
something is typed into the MODEL cell and an "X" is placed in either
the ZONE_I, ZONE_II or ZONE_III cells).

First, just to give you an idea, here is an example of one of the
formulas that is working correctly (notice that it replaced every
occurance of SALESFORM! with the name of the workbook, 'LEXINGTON_Sept
15 Pricing.xls'!.....This is not a problem, but I'm just curious as to
why it does this):

=IF(AND('LEXINGTON_Sept 15 Pricing.xls'!ZONE_I="X",'LEXINGTON_Sept 15
Pricing.xls'!MODEL>0),-900,IF(AND('LEXINGTON_Sept 15
Pricing.xls'!ZONE_II="X",'LEXINGTON_Sept 15
Pricing.xls'!MODEL>0),"INCL",IF('LEXINGTON_Sept 15
Pricing.xls'!ZONE_III="X",1040," ")))


Now, here is the formula I'm having a problem with:

ORIGINAL FORMULA (EXCEL SAYS THIS IS TOO LONG):

=IF(AND(ZONE_I="X",MODEL>0),IF(OR(MODEL="2465CT",MODEL="2466CT",MODEL="2467C
T",MODEL="2458CT",MODEL="2465CTH",MODEL="2466CTH",MODEL="2463CTH",MODEL="245
8CTH",MODEL="2464CTH",MODEL="2550CT",MODEL="2567CT",MODEL="2463CTP",MODEL="2
458CTP",MODEL="2464CTP",MODEL="2522CT",MODEL="2530CT"),-850,IF(OR(MODEL="252
5CT",MODEL="2526CT",MODEL="2527CT",MODEL="2528CT",MODEL="4301CT",MODEL="4302
CT"),-900,IF(OR(MODEL="2553CT",MODEL="2554CT",MODEL="2560CT",MODEL="2561CT",
MODEL="2523CT",MODEL="2531CT"),-950))),IF(ZONE_II="X","INCL",IF(AND(ZONE_III
="X",MODEL>0),IF(OR(MODEL="2465CT",MODEL="2466CT",MODEL="2467CT",MODEL="2458
CT",MODEL="2465CTH",MODEL="2466CTH",MODEL="2463CTH",MODEL="2458CTH",MODEL="2
464CTH",MODEL="2567CT",MODEL="2463CTP",MODEL="2458CTP",MODEL="2464CTP",MODEL
="2522CT",MODEL="2530CT"),845,IF(OR(MODEL="2550CT",MODEL="2553CT",MODEL="255
4CT",MODEL="2560CT",MODEL="2561CT",MODEL="2523CT",MODEL="2531CT",MODEL="4301
CT",MODEL="4302CT"),1040,IF(OR(MODEL="2525CT",MODEL="2526CT",MODEL="2527CT",
MODEL="2528CT"),995))),"
")))


FORMULA SPLIT INTO 3 PARTS:


PART I (first cell): This returns FALSE; I want it to be blank unless
an "X" is placed in Zone I.

=IF(AND(SALESFORM!ZONE_I="X",SALESFORM!MODEL>0),IF(OR(SALESFORM!MODEL="2465C
T",SALESFORM!MODEL="2466CT",SALESFORM!MODEL="2467CT",SALESFORM!MODEL="2458CT
",SALESFORM!MODEL="2465CTH",SALESFORM!MODEL="2466CTH",SALESFORM!MODEL="2463C
TH",SALESFORM!MODEL="2458CTH",SALESFORM!MODEL="2464CTH",SALESFORM!MODEL="255
0CT",SALESFORM!MODEL="2567CT",SALESFORM!MODEL="2463CTP",SALESFORM!MODEL="245
8CTP",SALESFORM!MODEL="2464CTP",SALESFORM!MODEL="2522CT",SALESFORM!MODEL="25
30CT"),"-850",IF(OR(SALESFORM!MODEL="2525CT",SALESFORM!MODEL="2526CT",SALESF
ORM!MODEL="2527CT",SALESFORM!MODEL="2528CT",SALESFORM!MODEL="4301CT",SALESFO
RM!MODEL="4302CT"),"-900",IF(OR(SALESFORM!MODEL="2553CT",SALESFORM!MODEL="25
54CT",SALESFORM!MODEL="2560CT",SALESFORM!MODEL="2561CT",SALESFORM!MODEL="252
3CT",SALESFORM!MODEL="2531CT"),"-950","
"))))


PART II (second cell): This one seems fine, possibly because it is not
too long. I'm wondering if the other two formulas are simply too
long.

=IF(AND(SALESFORM!ZONE_II="X",SALESFORM!MODEL>0),"INCL"," ")


PART III(third cell): This also returns FALSE; I want it to be blank
unless an "X" is placed in Zone III.
Click to expand...
=IF(AND(SALESFORM!ZONE_III="X",SALESFORM!MODEL>0),IF(OR(SALESFORM!MODEL="246
5CT",SALESFORM!MODEL="2466CT",SALESFORM!MODEL="2467CT",SALESFORM!MODEL="2458
CT",SALESFORM!MODEL="2465CTH",SALESFORM!MODEL="2466CTH",SALESFORM!MODEL="246
3CTH",SALESFORM!MODEL="2458CTH",SALESFORM!MODEL="2464CTH",SALESFORM!MODEL="2
567CT",SALESFORM!MODEL="2463CTP",SALESFORM!MODEL="2458CTP",SALESFORM!MODEL="
2464CTP",SALESFORM!MODEL="2522CT",SALESFORM!MODEL="2530CT"),"845",IF(OR(SALE
SFORM!MODEL="2550CT",SALESFORM!MODEL="2553CT",SALESFORM!MODEL="2554CT",SALES
FORM!MODEL="2560CT",SALESFORM!MODEL="2561CT",SALESFORM!MODEL="2523CT",SALESF
ORM!MODEL="2531CT",SALESFORM!MODEL="4301CT",SALESFORM!MODEL="4302CT"),"1040"
,IF(OR(SALESFORM!MODEL="2525CT",SALESFORM!MODEL="2526CT",SALESFORM!MODEL="25
27CT",SALESFORM!MODEL="2528CT"),"995",""))))
 
...
...
One cell I have renamed "MODEL". When you enter a Model number into
this cell, it will bring up a description and a base price.
I have 3 other cells, named "ZONE_I", "ZONE_II" and "ZONE_III",
respectively. When you place and "X" in one of these cells, it will
bring up a monetary amount to either add or subtract to or from that
base price, depending on where you place the "X". ...
First, just to give you an idea, here is an example of one of the
formulas that is working correctly (notice that it replaced every
occurance of SALESFORM! with the name of the workbook, 'LEXINGTON_Sept
15 Pricing.xls'!.....This is not a problem, but I'm just curious as to
why it does this):
Click to expand...

It shouldn't. What file format are you using when saving this file?
Now, here is the formula I'm having a problem with:

ORIGINAL FORMULA (EXCEL SAYS THIS IS TOO LONG):
Click to expand...
...

A fine example of when *NOT* to use complicated IF conditions. If you created a
table such as (underscores between columns)

Model_______Zone1_____Zone3
2458CT_______-850______845
2458CTH______-850______845
2458CTP______-850______845
2463CTH______-850______845
2463CTP______-850______845
2464CTH______-850______845
2464CTP______-850______845
2465CT_______-850______845
2465CTH______-850______845
2466CT_______-850______845
2466CTH______-850______845
2467CT_______-850______845
2522CT_______-850______845
2523CT_______-950_____1040
2525CT_______-900______995
2526CT_______-900______995
2527CT_______-900______995
2528CT_______-900______995
2530CT_______-850______845
2531CT_______-950_____1040
2550CT_______-850_____1040
2553CT_______-950_____1040
2554CT_______-950_____1040
2560CT_______-950_____1040
2561CT_______-950_____1040
2567CT_______-850______845
4301CT_______-900_____1040
4302CT_______-900_____1040

and named it something like TBL, you could use the formula

=IF(AND(ZONE_I="X",MODEL>0),VLOOKUP(MODEL,TBL,2,0),IF(ZONE_II="X","INCL",
IF(AND(ZONE_III="X",MODEL>0),VLOOKUP(MODEL,TBL,3,0))))

And as an added bonus, it'd be vastly simpler to maintain.

Another point: MODEL>0 may not always work. If the model entries are *TEXT*,
then you shouldn't compare them to numeric zero. You should compare them to "".
And I suspect that if there were no model entry or no Zone_* cell checked you'd
prefer showing nothing, "", rather than FALSE. So change the formula further to

=IF(MODEL="","",IF(ZONE_I="X",VLOOKUP(MODEL,TBL,2,0),IF(ZONE_II="X","INCL",
IF(ZONE_III="X",VLOOKUP(MODEL,TBL,3,0),"")))

At this point you could even use the first column of TBL as a data validation
list, so users could select model IDs from a drop-down list.
 
Back
Top