Rolling Average
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 |
Rolling Average
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 .... ... ... -- Thanks, Shane Devenshire "James" wrote: 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 |
Rolling Average
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 "ShaneDevenshire" wrote: 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 ... ... ... -- Thanks, Shane Devenshire "James" wrote: 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 |
All times are GMT +1. The time now is 11:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com