Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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. #3
    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: 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. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Jerry

  5. #5
    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: 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. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Jerry

  7. #7
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Re: Averaging across worksheets (97 SR2)

    Something like this?
    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Legare Coleman

Posting Permissions

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