G
GMCN
I currently generate a monthly report which consists of a number of
bar charts overlaid with running 3 month and 'forever' averages. Each
month I generate a new workbook which consists of that month's data
'dump' and 7 other sheets which have pivot tables generated from the
'dump' which I use to calculate the data required for each set of
charts. Each sheet contains data for 3 charts (each chart has 80 'X'
values with the 3 month and 'forever' averages overlaid). I have
another workbook which has a sheet for each new month into which I
copy the data from the workbooks previously described and another
sheet which I use to calculate the 'forever' average:
"=AVERAGE(1ST MONTH:CURRENT MONTH)"
E.g. "=AVERAGE(JUN06:FEB07)"
The problems I face with carrying out this task in this manner is
that it is very clunky and time consuming. Each month for the 3 month
running average I have to alter the formula to delete the oldest month
and insert the latest. Likewise for the 'forever' average I have to
replace the last current month and replace it with the latest. My
questions are:
1. Is there a method that I can use for calculating the 3 month
running average automatically?
2. Is there a method I can use for calculating the 'forever' average
from my current series of monthly workbooks to automatically include
each new month thereby negating the need for the extra 'forever'
average workbook?
I would be grateful for any assistance anyone can offer on this
(longwinded) matter.
Thanks
bar charts overlaid with running 3 month and 'forever' averages. Each
month I generate a new workbook which consists of that month's data
'dump' and 7 other sheets which have pivot tables generated from the
'dump' which I use to calculate the data required for each set of
charts. Each sheet contains data for 3 charts (each chart has 80 'X'
values with the 3 month and 'forever' averages overlaid). I have
another workbook which has a sheet for each new month into which I
copy the data from the workbooks previously described and another
sheet which I use to calculate the 'forever' average:
"=AVERAGE(1ST MONTH:CURRENT MONTH)"
E.g. "=AVERAGE(JUN06:FEB07)"
The problems I face with carrying out this task in this manner is
that it is very clunky and time consuming. Each month for the 3 month
running average I have to alter the formula to delete the oldest month
and insert the latest. Likewise for the 'forever' average I have to
replace the last current month and replace it with the latest. My
questions are:
1. Is there a method that I can use for calculating the 3 month
running average automatically?
2. Is there a method I can use for calculating the 'forever' average
from my current series of monthly workbooks to automatically include
each new month thereby negating the need for the extra 'forever'
average workbook?
I would be grateful for any assistance anyone can offer on this
(longwinded) matter.
Thanks