Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying formulas | Excel Worksheet Functions | |||
Two questions - copying and averaging | Excel Discussion (Misc queries) | |||
Copying formulas | Excel Discussion (Misc queries) | |||
Copying Sum Formulas | Excel Worksheet Functions | |||
Conditional Formulas For Averaging | Excel Discussion (Misc queries) |