Advanced Excel Averaging

[tbrule]

Hello all. I'm in desperate need of help with the following problem.
We are creating a workbook template that will be used to record
"scores" for a process self-assessment we will be conducting at
various locations. Each workbook will contain a worksheet for each
department to record their scores. The workbook will also contain a
worksheet that averages the scores from the various worksheet. A
workbook can contain any number of worksheet, herein lies the problem.

I need to figure out to use VB (or something) to average the scores on
the worksheet together...and do do it without know how many worksheets
are in the workbook

This is a difficult situation to explain to I'll try to simplify it.


For every worksheet called WS* (i.e., WS1, WS2, WS3, etc.) average the
value of cell C5.

I appreciate any suggestions. Thank you all!!!

 

[Ed Ferrero]

Hi tbrule,

The easy way is to keep all your data on one worksheet. Add a column for
department name. Filter the list by department.

The hard way you have discovered for yourself... ...is hard.

Ed Ferrero
www.edferrero.com

 

[Roger Govier]

Hi

You could use
=AVERAGE(WS1:WS10!C5)

That will give you an average of the values in C5.
The problem arises, in that if C5 is an average itself, then you are giving
equal weighting to each sheet, and there may be different counts on each
sheet, hence your Average will be incorrect.

One way around it might be to also store on each sheet, in say C6 and C7,
the Count of entries on the Sheet and the Sum of the values.
Then you could use
=SUM(WS1:WS10!C7)/SUM(WS1:WS10!C6)