Help with Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a workbook(1)that has links to 2 other workbooks (these 2 are
identical in design) I am trying to build up a year to date variance figure
in the 1st workbook and have the following formula:

=(('[MPG(SIC) Report 2004.xls]JAN'!$J$3+'[MPG(SIC) Report
2004.xls]FEB'!$J$3)-('[MPG(SIC) Report 2003.xls]JAN'!$J$3+'[MPG(SIC) Report
2003.xls]FEB'!$J$3))/('[MPG(SIC) Report 2003.xls]JAN'!$J$3+'[MPG(SIC) Report
2003.xls]FEB'!$J$3)

This gives me the variance for YTD JAN to FEB.

I need to extend this formula for each YTD month (eg, JAN to MAR, JAN to
APR, JAN to MAY etc...)

However I can see that my formula is going to be very long by the time i get
to JAN to DEC, and will not be easy for someone else to follow. Is there a
way of shortening this formula.

Regards
 
I don't think that this is shorter, but...

I'd dedicate a couple of columns--one for a description and one for the
value--for each of the values you're bringing back.

Then refer to those cells when you're calculating the variance.

And heck, since you have all the values, you could even do more--like Feb to
Nov.


I have a workbook(1)that has links to 2 other workbooks (these 2 are
identical in design) I am trying to build up a year to date variance figure
in the 1st workbook and have the following formula:

=(('[MPG(SIC) Report 2004.xls]JAN'!$J$3+'[MPG(SIC) Report
2004.xls]FEB'!$J$3)-('[MPG(SIC) Report 2003.xls]JAN'!$J$3+'[MPG(SIC) Report
2003.xls]FEB'!$J$3))/('[MPG(SIC) Report 2003.xls]JAN'!$J$3+'[MPG(SIC) Report
2003.xls]FEB'!$J$3)

This gives me the variance for YTD JAN to FEB.

I need to extend this formula for each YTD month (eg, JAN to MAR, JAN to
APR, JAN to MAY etc...)

However I can see that my formula is going to be very long by the time i get
to JAN to DEC, and will not be easy for someone else to follow. Is there a
way of shortening this formula.

Regards
Click to expand...
 
Back
Top