# Thread: Averaging across worksheets (97 SR2)

1. ## Averaging across worksheets (97 SR2)

Hello all.

I am putting together a workbook that will have many worksheets, all configured to the same template. I will take readings every day and enter the data in one worksheet per day. The data will be broken out into a set of percentages at the end of the day. The cells these percentages will be in will be the same on every worksheet.

I want to set up a master worksheet that will average across all of the data worksheets.

Am I making enough sense? It is late Friday afternoon after all.

Is there a way of looking at the same cell over many worksheets and pulling an average value out?

TIA, Douglas

2. ## Re: Averaging across worksheets (97 SR2)

It would be better to put the data in one worksheet, in one long table. This makes it much easier to perform calculations. You can always use another sheet to present and summarize the data for one day.

3. ## Re: Averaging across worksheets (97 SR2)

I agree completely with Hans. I am an opponent of creating multiple identically formatted sheets to hold data.

Putting the data into 1 sheet allows the use of many of excels "builtin" features. Autofilters can be used to chart and calculate (with subtotal function) only the data that is visible. Pivot tables can also be done easily to create summary tables of the data using (for example) page fields to pull only certain years and the dates can even be grouped.

Steve

4. ## Re: Averaging across worksheets (97 SR2)

In addition to what Steve and Hans advised, I had a similar problem a while back in having to consolidate a large amount of data from imported worksheets and wanted to consolidate the overall figures from these template.

I created a new worksheet ( it was part of my final consolidated workbook) that used a formula to pick up the calculations) called MasterData and a worksheet called end ( This was left blank)

The templated data was imported into the workbook using code and "sandwiched" between MasterDate and the end worksheet.

On the MasterData worksheet I put in the following formula (assume calculating the average percentage in the range of B2):

=AVERAGE(Data1:end!B2)

I have attached the workbook for you to see what I have done and hopefully make it a little clearer.

5. ## Re: Averaging across worksheets (97 SR2)

As stated, I am not a proponent of this approach. I think consolidating is a much better approach. See the example.

Steve

6. ## Re: Averaging across worksheets (97 SR2)

Agreed Steve

Just an opinion post in case Douglas had gone "too far down the road" with his application. My response was an example of when I had gone just that too far and had a short deadline.

Pivot tables are a very good route for the consolidation, thanks.

7. ## Re: Averaging across worksheets (97 SR2)

I have read your suggesttions and normally I would agree that it is better to have all the data on one sheet. In this instance however, I am having trouble seeing how to do it. It appears to me that I need more than the two dimensions offered in a flat worksheet. I am trying to work with 3-dimensional sets. I have attached my spreadsheet here so that you can see what I mean.

Douglas

8. ## Re: Averaging across worksheets (97 SR2)

Something like this?

9. ## Re: Averaging across worksheets (97 SR2)

Yes, exactly like that. That's what I was looking for. Thanks.

Do others see a problem with using this technique?

Douglas

10. ## Re: Averaging across worksheets (97 SR2)

Like Hans and Steve, I don't like the workbook set up this way, I think that it is asking for problems. I would prefer one worksheet with a new row for each new set of data. That would make analyzing the data much easier.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•