R
rodw
I had a problem which others might come across, so I thought I'd post
the answer here in case anyone else comes across the same issue (or
anyone has a better solution).
Here's the original problem:
This one is a "There simply must be an easier way to do this" question
I'm hoping someone has an answer to.
I have an Excel workbook (Excel 2007) which has multiple (30)
worksheets, each of which are in exactly the same format with data for
each individual in a separate sheet (each subject's individual test
sub-scores for things like strength, flexibility, and the like, for
both their left and right sides, and there are about 30 tests/measure
for each subject, each side) that I would like to summarise.
That means there are 60 measures per subject, 30 subjects, so 1,800
data points I'm trying to summarise to 60 averages, and standard
deviations, max and min.
I thought this would be something Excel would have a built-in
function for, and the closest thing seems to be "Data – Consolidate –
Position" but it looks like it involves choosing the function, and
then individually choosing each and every cell in each of the sheets I
want to average.
This is no easier than simply writing the same function in a cell, and
makes me think I am missing something.
After a bit of to and fro on the Whirlpool.net.au Windows forums,
here's the washup:
AdamL had a good idea:
Say you had a value in cell B2 in each of 60 sheets, and you wanted to
average all of these values.
In another sheet, list all the sheet names (say in G1:G60).
Then in B2 of that sheet, Ctrl+Shift+enter:
=AVERAGE(N(INDIRECT("'"&$G$1:$G$60&"!'R"&ROWS(B$1:B2)&"C"&COLUMNS
($A2:B2),0)))
I had forgotten about indirect referencing and concatenating functions
to make up a string, and the Ctrl-Shift-Enter trick. This method
almost certainly will work, but for my purposes, it would be prone to
error as each sheet is named with the individual player's name, so I
would also be adding in space characters which I would first have to
find, and there inevitably is the chance of a typo bringing the whole
thing undone.
In the end, what worked was a combination of the built-in Data-
Consolidate function, and a 3rd party extension to this which makes
the interface a little cleaner.
In my case I used the Office Assistance Advanced Consolidation Manager
as it had a 20 day trial period, but there are plenty on the net, and
I'm sure if I looked for long enough, there would be a free version
somewhere.
If there is a more elegant solution, I'd love to hear it as this will
come up again one day.
the answer here in case anyone else comes across the same issue (or
anyone has a better solution).
Here's the original problem:
This one is a "There simply must be an easier way to do this" question
I'm hoping someone has an answer to.
I have an Excel workbook (Excel 2007) which has multiple (30)
worksheets, each of which are in exactly the same format with data for
each individual in a separate sheet (each subject's individual test
sub-scores for things like strength, flexibility, and the like, for
both their left and right sides, and there are about 30 tests/measure
for each subject, each side) that I would like to summarise.
That means there are 60 measures per subject, 30 subjects, so 1,800
data points I'm trying to summarise to 60 averages, and standard
deviations, max and min.
I thought this would be something Excel would have a built-in
function for, and the closest thing seems to be "Data – Consolidate –
Position" but it looks like it involves choosing the function, and
then individually choosing each and every cell in each of the sheets I
want to average.
This is no easier than simply writing the same function in a cell, and
makes me think I am missing something.
After a bit of to and fro on the Whirlpool.net.au Windows forums,
here's the washup:
AdamL had a good idea:
Say you had a value in cell B2 in each of 60 sheets, and you wanted to
average all of these values.
In another sheet, list all the sheet names (say in G1:G60).
Then in B2 of that sheet, Ctrl+Shift+enter:
=AVERAGE(N(INDIRECT("'"&$G$1:$G$60&"!'R"&ROWS(B$1:B2)&"C"&COLUMNS
($A2:B2),0)))
I had forgotten about indirect referencing and concatenating functions
to make up a string, and the Ctrl-Shift-Enter trick. This method
almost certainly will work, but for my purposes, it would be prone to
error as each sheet is named with the individual player's name, so I
would also be adding in space characters which I would first have to
find, and there inevitably is the chance of a typo bringing the whole
thing undone.
In the end, what worked was a combination of the built-in Data-
Consolidate function, and a 3rd party extension to this which makes
the interface a little cleaner.
In my case I used the Office Assistance Advanced Consolidation Manager
as it had a 20 day trial period, but there are plenty on the net, and
I'm sure if I looked for long enough, there would be a free version
somewhere.
If there is a more elegant solution, I'd love to hear it as this will
come up again one day.