# Thread: Average from Multiple Worksheets (2002/SP2)

1. ## Average from Multiple Worksheets (2002/SP2)

I am going to be tracking some data over about a four week period. I will be downloading from another program and then placing into a workbook. I am planning to have a seperate worksheet, or Tab, for the data for each day. What I want to do is to have another worksheet that will show the average for each cell, from all worksheets. For example, I want to see the average of B10 from all worksheets. How do I write the formula to cover all worksheets, without having to enter in each location one at a time, and is there a way to update it when I add another page, without having to re-write the formulas? Thanks for any help.

2. ## Re: Average from Multiple Worksheets (2002/SP2)

Unless the total would be over 65,536 rows, it would be better to put all data in a single table in a single worksheet. That would make it much easier to set up the formulas.

3. ## Re: Average from Multiple Worksheets (2002/SP2)

If the values to be averaged are on Sheets 2 through 6, then:

<pre>=AVERAGE(Sheet2:Sheet6!B10)
</pre>

4. ## Re: Average from Multiple Worksheets (2002/SP2)

And if you use Legare's approach, if you insert the new sheet within the range, it will also be included in the calculations.

Steve

5. ## Re: Average from Multiple Worksheets (2002/SP2)

Thanks for your help everyone. Hans, I understand exactly what you were saying, I just wanted to make it easy to find each day.

I had to do the formula slightly different, like this, =AVERAGE('2_24_05:2_22_05'!B10:B10), where '2_24_05:2_22_05' is the name of the sheets, but instead of !B10, I had to put in !B10:B10 and that gave me the correct averages. Thanks again for all the help.

#### Posting Permissions

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