Reducing Formular length
 

Hi I have a formular that is very long in a workbook I am currently using.
How can I reduce the length of teh formular? The sample formular is presented
below:

='Deployment Cost WorkSheet'!$D50*'Current Dep Plan &
Expec.'!E$14+'Deployment Cost WorkSheet'!$E50*'Current Dep Plan &
Expec.'!E$17+'Deployment Cost WorkSheet'!$F50*'Current Dep Plan &
Expec.'!E$20+'Deployment Cost WorkSheet'!$G50*'Current Dep Plan &
Expec.'!E$23+'Deployment Cost WorkSheet'!$H50*'Current Dep Plan &
Expec.'!E$26+'Deployment Cost WorkSheet'!$I50*'Current Dep Plan &
Expec.'!E$29+'Deployment Cost WorkSheet'!$J50*'Current Dep Plan &
Expec.'!E$32+'Deployment Cost WorkSheet'!$K50*'Current Dep Plan &
Expec.'!E$35+'Deployment Cost WorkSheet'!$L50*'Current Dep Plan &
Expec.'!E$38+'Deployment Cost WorkSheet'!$M50*'Current Dep Plan &
Expec.'!E$41+'Deployment Cost WorkSheet'!$N50*'Current Dep Plan & Expec.'!E$45

Any suggestions will be helpful.

Nello,

There is no easy way to do that, since you have unequal offsets. But, you
could simplify your formula by using helper cells:

In cell A1, enter the formula:

=OFFSET('Current Dep Plan & Expec.'!$E$14,(COLUMN()-1)*3,0)

In cell A2, enter the formula:
='Deployment Cost WorkSheet'!D50

Copy those two cells, and paste into B1:K2.

Then use the formula
=SUMPRODUCT(A1:K1,A2:K2)

to give the same result as your long formula.

Using this technique makes it easier to extend the formula when needed.

HTH,
Bernie
MS Excel MVP


"Nello" wrote in message
...
Hi I have a formular that is very long in a workbook I am currently using.
How can I reduce the length of teh formular? The sample formular is
presented
below:

='Deployment Cost WorkSheet'!$D50*'Current Dep Plan &
Expec.'!E$14+'Deployment Cost WorkSheet'!$E50*'Current Dep Plan &
Expec.'!E$17+'Deployment Cost WorkSheet'!$F50*'Current Dep Plan &
Expec.'!E$20+'Deployment Cost WorkSheet'!$G50*'Current Dep Plan &
Expec.'!E$23+'Deployment Cost WorkSheet'!$H50*'Current Dep Plan &
Expec.'!E$26+'Deployment Cost WorkSheet'!$I50*'Current Dep Plan &
Expec.'!E$29+'Deployment Cost WorkSheet'!$J50*'Current Dep Plan &
Expec.'!E$32+'Deployment Cost WorkSheet'!$K50*'Current Dep Plan &
Expec.'!E$35+'Deployment Cost WorkSheet'!$L50*'Current Dep Plan &
Expec.'!E$38+'Deployment Cost WorkSheet'!$M50*'Current Dep Plan &
Expec.'!E$41+'Deployment Cost WorkSheet'!$N50*'Current Dep Plan &
Expec.'!E$45

Any suggestions will be helpful.

Love those long sheet names! <hint

Biff

"Nello" wrote in message
...
Hi I have a formular that is very long in a workbook I am currently using.
How can I reduce the length of teh formular? The sample formular is
presented
below:

='Deployment Cost WorkSheet'!$D50*'Current Dep Plan &
Expec.'!E$14+'Deployment Cost WorkSheet'!$E50*'Current Dep Plan &
Expec.'!E$17+'Deployment Cost WorkSheet'!$F50*'Current Dep Plan &
Expec.'!E$20+'Deployment Cost WorkSheet'!$G50*'Current Dep Plan &
Expec.'!E$23+'Deployment Cost WorkSheet'!$H50*'Current Dep Plan &
Expec.'!E$26+'Deployment Cost WorkSheet'!$I50*'Current Dep Plan &
Expec.'!E$29+'Deployment Cost WorkSheet'!$J50*'Current Dep Plan &
Expec.'!E$32+'Deployment Cost WorkSheet'!$K50*'Current Dep Plan &
Expec.'!E$35+'Deployment Cost WorkSheet'!$L50*'Current Dep Plan &
Expec.'!E$38+'Deployment Cost WorkSheet'!$M50*'Current Dep Plan &
Expec.'!E$41+'Deployment Cost WorkSheet'!$N50*'Current Dep Plan &
Expec.'!E$45

Any suggestions will be helpful.