combining worksheets

D

datamodeler

I have a number of identically structured worksheets. I wish to treat
them as alternative realities, assigning a probability of occurrence
to each. I want to combine all this into a probabilistically "average"
worksheet.

Anybody have a good way to do this?

Thanks

mike
 
D

Daryl S

Mike -

You can set up one spreadsheet with the spreadsheets listed and the
weighting you want to assign to each. Then set up another spreadsheet that
will contain the results. Copy any header / row information from one of the
spreadsheets, then for each cell you want 'averaged', enter a formula that
will do the weighting. For example, if cell B2 in each spreadsheet is data
you want weight-averaged, and you have 5 spreadsheets to average, and the
weighting is in the worksheet WEIGHTINGS, you would have something like this:

=Worksheet1!B2*WEIGHTINGS!$B$1 + Worksheet2!B2*WEIGHTINGS!$B$2 +
Worksheet3!B2*WEIGHTINGS!$B$3 + Worksheet4!B2*WEIGHTINGS!$B$4 +
Worksheet5!B2*WEIGHTINGS!$B$5

This assumes the WEIGHTINGS worksheet has the spreadsheet names in column A
and the weightings in column B, with the weighting value for worksheet1 in
cell B2, etc, and the weightings all are percents that add up to 1.00.

Once you have the first cell in the Average worksheet set up, you can
copy/paste it to any of the other cells that need averaging (only numeric
cells will work for this). You will need to decide if you should re-sum or
calculate any values in the Average worksheet, or if those sums should be
weighted averages also.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top