Rolling Average

[James]

Using Excel 2003

This is a simplified example of the real task which involves a larger golf
rating scorecard used for a college golf team's average performance which
drives the development of a practice plan.

This request is similar to a problem posted by Ian G ... with some
differences ...

Difference 1: I am using seperate worksheets for the data input table and
an average summary and calculation table

D 2: I would like to automate the data transfer from Wk Sh 1 Data Entry
Table to Wk Sh 2 Calculations Table. I tried to use some long macros for
this transfer, but it failed,.

Visualize Wk Sh 1 as ... 5 columns X 4 rows ...
R1 is a header row containing labels ... A1= blank, B1=Day 1, C1=Day 2,
D1=Day 3, E1=AVE
Col A contains row/record labels ... A2=Joe, A3=Jak, A4=Jon
Col E contains Ave Function calculations for corresponding row/records

Visualize Wk Sh 2 as a summary and average of the average (from Wk Sh 2) ...
7 columns X 4 rows ...
R1 is a head row containing labels ... A1=Blank, B1=Ave, C1=Rd 1, D1=Rd 2,
E1=Rd 3, F1=Rd 4, G1=Rd 5 (... new data is added daily, growing the
worksheet; the average calculation is made in Col B so it can always been
seen/printed on page 1 as a summary area)
Col A contains row/record labels ... A2=Joe, A3=Jak, A4=Jon
Col B contans Ave Function for Corresponding row/records

Problem 1 ... change the data input to Wk Sh 1 every day and automatically
add this new Col of averaged data to Wk Sh 2 ... this is the larger problem,
automatic data transfer to a new work sheet ... this is where I tried to use
macros

Problem 2 ... as Wk Sh 2 table grows, use only the newest 10 days data for
the average of the average calculation ... preferably, the oldest scores
would "roll off" the Wk Sh as newer scores are added (desired, but not
necessary)

I hope someone is able to visualize the problem from this lengthy description

 

[ShaneDevenshire]

It always helps if you post sample data rather than a wordy description:

1. What does the source data look like:
A B C
xx yy zz
... ... ..

2. What do you want as the resulting output:
M N O
jjj kkk lll
.... ... ...

 

[James]

Wk Sh 1 Structure of Rows and Columns

Day 1 Day 2 Day 3 AVE
Joe 1 2 3 2
Jak 4 5 6 5
Jon 7 8 9 8

Wk Sh 2 Structure of Rows and Columns

Ave Day 1 Day 2 Day 3 Day 4 Day 5
Joe 2 1 2 3
Jak 6 4 5 6 7 8
Jon 8 8 8