copying & averaging formulas
 

I hope you can help me.
I keep a monthly spreadsheet of sales numbers. each month has its own tab.
I have an average ytd column that takes the average from each subsequent
month, then adds to the current month to come up with a YTD average. As the
months go on this gets bigger and more difficult to manage.

here is an example of one formula =('Jan 07 Pace Final'!E7+'Feb 07 Pace
Final'!E7+'Mar 07 Pace Final'!E7+'Apr 07 Pace Final'!E7+'May 07 Pace
Final'!E7+E7+'June 07 Pace Final'!E7+'July 07 Pace'!E7)/('Jan 07 Pace
Final'!B7+'Feb 07 Pace Final'!B7+'Mar 07 Pace Final'!B7+'Apr 07 Pace
Final'!B7+'May 07 Pace Final'!B7+B7+'June 07 Pace Final'!B7+'July 07 Pace'!B7)

is there any way to make this more efficient and easier to use?
thanks for all your help in advance!

copying & averaging formulas
 

One way:

=SUM('Jan 07 Pace Final:July 07 Pace'!E7)/SUM('Jan 07 Pace
Final:July 07 Pace'!B7)


In article ,
xcel user <xcel wrote:

I hope you can help me.
I keep a monthly spreadsheet of sales numbers. each month has its own tab.
I have an average ytd column that takes the average from each subsequent
month, then adds to the current month to come up with a YTD average. As the
months go on this gets bigger and more difficult to manage.

here is an example of one formula =('Jan 07 Pace Final'!E7+'Feb 07 Pace
Final'!E7+'Mar 07 Pace Final'!E7+'Apr 07 Pace Final'!E7+'May 07 Pace
Final'!E7+E7+'June 07 Pace Final'!E7+'July 07 Pace'!E7)/('Jan 07 Pace
Final'!B7+'Feb 07 Pace Final'!B7+'Mar 07 Pace Final'!B7+'Apr 07 Pace
Final'!B7+'May 07 Pace Final'!B7+B7+'June 07 Pace Final'!B7+'July 07 Pace'!B7)

is there any way to make this more efficient and easier to use?
thanks for all your help in advance!

copying & averaging formulas
 

One way:

=SUM('Jan 07 Pace Final:Jul 07 Pace Final'!E7)/SUM('Jan 07 Pace Final:Jul 07
Pace Final'!B7)

Another way is to insert a couple of blank "Dummy" sheets at the beginning
and end of your sheet tabs, and name them:
Start
and
End

They should "sandwich" *ALL* the sheets that you wish to include in the
calculations,
THEN ... the formula becomes:

=SUM(Start:End!E7)/SUM(Start:End!B7)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"xcel user" <xcel wrote in message
...
I hope you can help me.
I keep a monthly spreadsheet of sales numbers. each month has its own
tab.
I have an average ytd column that takes the average from each subsequent
month, then adds to the current month to come up with a YTD average. As
the
months go on this gets bigger and more difficult to manage.

here is an example of one formula =('Jan 07 Pace Final'!E7+'Feb 07 Pace
Final'!E7+'Mar 07 Pace Final'!E7+'Apr 07 Pace Final'!E7+'May 07 Pace
Final'!E7+E7+'June 07 Pace Final'!E7+'July 07 Pace'!E7)/('Jan 07 Pace
Final'!B7+'Feb 07 Pace Final'!B7+'Mar 07 Pace Final'!B7+'Apr 07 Pace
Final'!B7+'May 07 Pace Final'!B7+B7+'June 07 Pace Final'!B7+'July 07
Pace'!B7)

is there any way to make this more efficient and easier to use?
thanks for all your help in advance!

copying & averaging formulas
 

Note that 'Jan 07 Pace Final' sheet tab would need to be on the left and
the 'July 07 Pace' sheet tab on the right, with the listed sheet tabs in
between.

In article ,
JE McGimpsey wrote:

One way:

=SUM('Jan 07 Pace Final:July 07 Pace'!E7)/SUM('Jan 07 Pace
Final:July 07 Pace'!B7)

copying & averaging formulas
 

I'm pretty sure John, that XL will automatically put them in the correct
order for you.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JE McGimpsey" wrote in message
...
Note that 'Jan 07 Pace Final' sheet tab would need to be on the left and
the 'July 07 Pace' sheet tab on the right, with the listed sheet tabs in
between.

In article ,
JE McGimpsey wrote:

One way:

=SUM('Jan 07 Pace Final:July 07 Pace'!E7)/SUM('Jan 07 Pace
Final:July 07 Pace'!B7)

copying & averaging formulas
 

Hi and thanks for your input. That would help but the problem is that the
cell ref number changes frequently. So while user 1 might be in E6 for jan,
feb and march, he might then be in E7 for april and May and so on.
Every month i copy the spreadsheet forward then I have to check every entry
to make sure the formula did not change during copying.

"JE McGimpsey" wrote:

One way:

=SUM('Jan 07 Pace Final:July 07 Pace'!E7)/SUM('Jan 07 Pace
Final:July 07 Pace'!B7)


In article ,
xcel user <xcel wrote:

I hope you can help me.
I keep a monthly spreadsheet of sales numbers. each month has its own tab.
I have an average ytd column that takes the average from each subsequent
month, then adds to the current month to come up with a YTD average. As the
months go on this gets bigger and more difficult to manage.

here is an example of one formula =('Jan 07 Pace Final'!E7+'Feb 07 Pace
Final'!E7+'Mar 07 Pace Final'!E7+'Apr 07 Pace Final'!E7+'May 07 Pace
Final'!E7+E7+'June 07 Pace Final'!E7+'July 07 Pace'!E7)/('Jan 07 Pace
Final'!B7+'Feb 07 Pace Final'!B7+'Mar 07 Pace Final'!B7+'Apr 07 Pace
Final'!B7+'May 07 Pace Final'!B7+B7+'June 07 Pace Final'!B7+'July 07 Pace'!B7)

is there any way to make this more efficient and easier to use?
thanks for all your help in advance!

copying & averaging formulas
 

Have you given any thought to using templates, so that everything would be
consistent?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"xcel user" wrote in message
...
Hi and thanks for your input. That would help but the problem is that the
cell ref number changes frequently. So while user 1 might be in E6 for
jan,
feb and march, he might then be in E7 for april and May and so on.
Every month i copy the spreadsheet forward then I have to check every
entry
to make sure the formula did not change during copying.

"JE McGimpsey" wrote:

One way:

=SUM('Jan 07 Pace Final:July 07 Pace'!E7)/SUM('Jan 07 Pace
Final:July 07 Pace'!B7)


In article ,
xcel user <xcel wrote:

I hope you can help me.
I keep a monthly spreadsheet of sales numbers. each month has its own
tab.
I have an average ytd column that takes the average from each
subsequent
month, then adds to the current month to come up with a YTD average. As
the
months go on this gets bigger and more difficult to manage.

here is an example of one formula =('Jan 07 Pace Final'!E7+'Feb 07 Pace
Final'!E7+'Mar 07 Pace Final'!E7+'Apr 07 Pace Final'!E7+'May 07 Pace
Final'!E7+E7+'June 07 Pace Final'!E7+'July 07 Pace'!E7)/('Jan 07 Pace
Final'!B7+'Feb 07 Pace Final'!B7+'Mar 07 Pace Final'!B7+'Apr 07 Pace
Final'!B7+'May 07 Pace Final'!B7+B7+'June 07 Pace Final'!B7+'July 07
Pace'!B7)

is there any way to make this more efficient and easier to use?
thanks for all your help in advance!