formula too long

[manman]

I need to have 2 mores options (ie HVAC_4 & HVAC_5)I am aware that I am
limited to 256 characters and making the range names smaller may help,
however is there another way of doing this that may eliminate the limitation.
I may also be reached during working hours at my office e-mail:
(e-mail address removed)
Regards

Leslie



=IF(OR(X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+3,1)),X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+6,1)),X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+9,1)),X$9=Budget_year)*AND(contract!$E12="Quarterly"),HVAC_1/4,IF(contract!$E12="monthly",HVAC_1/12,IF(AND(contract!$F12=X$9,contract!$E12="annual"),HVAC_1,0)))+IF(OR(X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+3,1)),X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+6,1)),X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+9,1)),X$9=Budget_year)*AND(contract!$E13="Quarterly"),HVAC_2/4,IF(contract!$E13="monthly",HVAC_2/12,IF(AND(contract!$F13=X$9,contract!$E13="annual"),HVAC_2)))+IF(OR(X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+3,1)),X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+6,1)),X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+9,1)),X$9=Budget_year)*AND(contract!$E14="Quarterly"),HVAC_3/4,IF(contract!$E14="monthly",HVAC_3/12,IF(AND(contract!$F14=X$9,contract!$E14="annual"),HVAC_3)))

 

[Harlan Grove]

I need to have 2 mores options (ie HVAC_4 & HVAC_5)I am aware that I am
limited to 256 characters and making the range names smaller may help,
however is there another way of doing this that may eliminate the
limitation.

....

[reformatted]
=IF(
OR(
X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+3,1)),
X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+6,1)),
X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+9,1)),
X$9=Budget_year
)*AND(contract!$E12="Quarterly"),
HVAC_1/4,
IF(
contract!$E12="monthly",
HVAC_1/12,
IF(
AND(
contract!$F12=X$9,
contract!$E12="annual"
),
HVAC_1,
0
)
)
)
+IF(
OR(
X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+3,1)),
X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+6,1)),
X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+9,1)),
X$9=Budget_year
)*AND(contract!$E13="Quarterly"),
HVAC_2/4,
IF(
contract!$E13="monthly",
HVAC_2/12,
IF(
AND(
contract!$F13=X$9,
contract!$E13="annual"
),
HVAC_2
)
)
)
+IF(
OR(
X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+3,1)),
X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+6,1)),
X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+9,1)),
X$9=Budget_year
)*AND(contract!$E14="Quarterly"),
HVAC_3/4,
IF(
contract!$E14="monthly",
HVAC_3/12,
IF(
AND(
contract!$F14=X$9,
contract!$E14="annual"
),
HVAC_3
)
)
)


You're performing the longest test once for each HVAC_#. That's where you
should start to make the formula shorter.


=IF(
OR(
X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+3,1)),
X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+6,1)),
X$9=(DATE(YEAR(Budget_year),MONTH(Budget_year)+9,1)),
X$9=Budget_year
),
IF(contract!$E12="Quarterly",HVAC_1/4,0)
+IF(contract!$E13="Quarterly",HVAC_2/4,0)
+IF(contract!$E14="Quarterly",HVAC_3/4,0)
+IF(contract!$E15="Quarterly",HVAC_4/4,0)
+IF(contract!$E16="Quarterly",HVAC_5/4,0),
0
)
+IF(contract!$E12="monthly",HVAC_1/12,0)
+IF(contract!$E13="monthly",HVAC_2/12,0)
+IF(contract!$E14="monthly",HVAC_3/12,0)
+IF(contract!$E15="monthly",HVAC_4/12,0)
+IF(contract!$E16="monthly",HVAC_5/12,0)
+IF(AND(contract!$F12=X$9,contract!$E12="annual"),HVAC_1,0)
+IF(AND(contract!$F13=X$9,contract!$E13="annual"),HVAC_2,0)
+IF(AND(contract!$F14=X$9,contract!$E14="annual"),HVAC_3,0)
+IF(AND(contract!$F15=X$9,contract!$E15="annual"),HVAC_4,0)
+IF(AND(contract!$F16=X$9,contract!$E16="annual"),HVAC_5,0)


953 characters, but that could be improved upon since there's a lot of
redundancy in these terms. Also, get rid of the unnecessary parentheses.


=SUMPRODUCT(({1;0;0;0;0}*HVAC_1+{0;1;0;0;0}*HVAC_2+{0;0;1;0;0}*HVAC_3
+{0;0;0;1;0}*HVAC_4+{0;0;0;0;1}*HVAC_5)*((X$9=DATE(YEAR(Budget_year),
MONTH(Budget_year)+{0,3,6,9},DAY(Budget_year)^{1,0,0,0}))
*(contract!$E12:$E16="Quarterly")+(contract!$E12:$E16="monthly")/12
+(contract!$F12:$F16=X$9)*(contract!$E12:$E16="annual"))/4)


Also, if you're entering this formula in a cell in the contract worksheet,
you could delete the unnecessary references to it.


=SUMPRODUCT(({1;0;0;0;0}*HVAC_1+{0;1;0;0;0}*HVAC_2+{0;0;1;0;0}*HVAC_3
+{0;0;0;1;0}*HVAC_4+{0;0;0;0;1}*HVAC_5)*((X$9=DATE(YEAR(Budget_year),
MONTH(Budget_year)+{0,3,6,9},DAY(Budget_year)^{1,0,0,0}))
*($E12:$E16="Quarterly")+($E12:$E16="monthly")/12
+($F12:$F16=X$9)*($E12:$E16="annual"))/4)