Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    556
    Thanks
    17
    Thanked 2 Times in 2 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    556
    Thanks
    17
    Thanked 2 Times in 2 Posts

    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
  •