Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula too long
"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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing cells takes long, long time | Excel Discussion (Misc queries) | |||
Formula is too long | Excel Discussion (Misc queries) | |||
Formula too long | Excel Discussion (Misc queries) | |||
formula is too long | Excel Worksheet Functions | |||
long formula | Excel Worksheet Functions |