rodwhiteley
New Member
- Joined
- Jan 15, 2012
- Messages
- 37
Apologies if I am not posting in the correct forum, please feel free to move if it needs to be.
We are in the middle of collecting a pretty big set of data that we are about to summarise so we can then analyse, and would appreciate some help in either creating a Macro, or writing a little bit of VBA to automate a task that is pretty tedious and likely error prone otherwise. This may even be a job for PowerPivot, but I'm not sure.
Background
The experiment we are doing had people running on a treadmill in 36 different conditions while we measured electrical activity in 12 muscles (6 on each leg).
We have broken the running down into strides (going from one time the foot hits the ground to the next time it hits the ground) and then each of these strides is split into 100 time points where we record the electrical activity of the 12 muscles.
For each trial condition, we have a left stride condition and a right stride condition. Each of the data sheets is named to reflect this so that the left leg for trial 1 is called: '1L' whereas the right leg for trial 33 would be '33R'.
The order of the trials was randomised, so each workbook will have the 36 conditions presented in different orders, but the left and right leg trials will always be one after the other, ie 12L then 12R then maybe 26L then 26R and so on (total of 72 sheets).
For each of these trials, the subject has somewhere between 5 at 40 strides of data (so somewhere between 5 and 40 columns of data in each sheet).
Sheets
The data is set up so that there are always 106 rows for each of the muscle groups, and the muscle groups always appear in the same order.
There are always the following 3 rows of data above the actual data: File name the data came from; Name of the muscle group being analysed, number of stride cycles
i.e.:
<tbody>
</tbody>Question
What I need to create is a way of automating the process of generating the summary sheet ('Summary23L') from the raw data ('23L') that I have created here:
http://1drv.ms/19VQxTE
This sheet simply takes the average of all the strides for each of the 100 parts of the gait cycle, and places it on the appropriate cell, then graphs it. The graph is purely for error-checking.
To keep file size down, this workbook only has one data sheet and the one summary sheet. What I am hoping to end up with is one summary sheet with the 72 trials placed one below the next as is seen for the first example in 'Summary23L', ie the range A1:M101, with the 2 adjacent error checking graphs, so this would ultimately go down to about row 7272
Currently this is being done by simply selecting the first row in the range for each muscle group (=AVERAGE('23L'!A5:AH5)) then do the same for the next 11 muscles, (=AVERAGE('23L'!A111:AH111) ... etc ) then fill the whole top row (B2:M2) down to the 100th row.
With the template sheet made this means 12 X "=AVERAGE(..." X 72 times for each subject (864 times) and repeating this for the 60 subjects (ie 51840 times) and that way lies madness.
I hope this is clear.
Hardware/Software available:
This is being done on a couple of Windows 7 machines using Excel 2013, one is 32Bit and one is 64Bit (8Gb RAM).
Thanks in advance for any help you can give me,
Rod
We are in the middle of collecting a pretty big set of data that we are about to summarise so we can then analyse, and would appreciate some help in either creating a Macro, or writing a little bit of VBA to automate a task that is pretty tedious and likely error prone otherwise. This may even be a job for PowerPivot, but I'm not sure.
Background
The experiment we are doing had people running on a treadmill in 36 different conditions while we measured electrical activity in 12 muscles (6 on each leg).
We have broken the running down into strides (going from one time the foot hits the ground to the next time it hits the ground) and then each of these strides is split into 100 time points where we record the electrical activity of the 12 muscles.
For each trial condition, we have a left stride condition and a right stride condition. Each of the data sheets is named to reflect this so that the left leg for trial 1 is called: '1L' whereas the right leg for trial 33 would be '33R'.
The order of the trials was randomised, so each workbook will have the 36 conditions presented in different orders, but the left and right leg trials will always be one after the other, ie 12L then 12R then maybe 26L then 26R and so on (total of 72 sheets).
For each of these trials, the subject has somewhere between 5 at 40 strides of data (so somewhere between 5 and 40 columns of data in each sheet).
Sheets
The data is set up so that there are always 106 rows for each of the muscle groups, and the muscle groups always appear in the same order.
There are always the following 3 rows of data above the actual data: File name the data came from; Name of the muscle group being analysed, number of stride cycles
i.e.:
| LRun_number_9_Plot_and_Store_Rep_2.1.hpf.mat |
| L SOLEUS LATERAL: EMG 1 |
| Cycles: 34 There are then 1 blank row, followed by the 100 rows of data (in this case 34 columns wide) then 2 blank rows, then the data for the next muscle group presented in the same format. |
<tbody>
</tbody>
What I need to create is a way of automating the process of generating the summary sheet ('Summary23L') from the raw data ('23L') that I have created here:
http://1drv.ms/19VQxTE
This sheet simply takes the average of all the strides for each of the 100 parts of the gait cycle, and places it on the appropriate cell, then graphs it. The graph is purely for error-checking.
To keep file size down, this workbook only has one data sheet and the one summary sheet. What I am hoping to end up with is one summary sheet with the 72 trials placed one below the next as is seen for the first example in 'Summary23L', ie the range A1:M101, with the 2 adjacent error checking graphs, so this would ultimately go down to about row 7272
Currently this is being done by simply selecting the first row in the range for each muscle group (=AVERAGE('23L'!A5:AH5)) then do the same for the next 11 muscles, (=AVERAGE('23L'!A111:AH111) ... etc ) then fill the whole top row (B2:M2) down to the 100th row.
With the template sheet made this means 12 X "=AVERAGE(..." X 72 times for each subject (864 times) and repeating this for the 60 subjects (ie 51840 times) and that way lies madness.
I hope this is clear.
Hardware/Software available:
This is being done on a couple of Windows 7 machines using Excel 2013, one is 32Bit and one is 64Bit (8Gb RAM).
Thanks in advance for any help you can give me,
Rod
