"manman" wrote...
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="ann ual"))/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)