Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Averaging groups of cells through a workbook (2002)

    I have a workbook with 31 sheets (Sheet1:Sheet31). Cells A1 through A5 all have readings on these sheets. How can I find the lowest average of the range A1:A5 per sheet for the entire workbook? I don't want to have to average the range into a cell on each sheet first. Thanks

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Averaging groups of cells through a workbook (2002)

    I don't think it's possible to do it in a formula (it could be done with some VBA code). Did you realize that you can group the sheets and enter the same average formula in all sheets in one shot (in cell A6, say)?

  3. #3
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging groups of cells through a workbook (2002)

    The sheets in the workbook duplicate a form that operators fill out on a daily basis. I would rather not add cells that are not part of the original form to each sheet if I don't have to. However, we do what we have to do. Can you explain how to add formulas to a number of sheets in one shot? Thanks

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Averaging groups of cells through a workbook (2002)

    On second thoughts, you could do it as shown in the attached workbook. Take a look and post back if you have questions.
    As far as inputing to multiple sheets at a time, all you have to do is "group" the sheets first. To do this select Sheet1, make Sheet31 visible, hold the shift key down, then select Sheet31. This puts you into "Group Mode" - as displayed in the title bar. Just don't forget to turn it off, because whatever you do you're doing it in all the sheets while it's on.

  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 groups of cells through a workbook (2002)

    I did not try this, but this ARRAY formula should work (confirm with ctrl-shift-enter):

    =min(average(indirect("Sheet"&ROW(indirect("1:31") )&"!$A$1:$A$5")))

    Steve

  6. #6
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging groups of cells through a workbook (2002)

    Steve, Your formula works just as I'd hoped. Thanks
    And thanks to Colinburrows for his time and the tip on grouping work sheets.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging groups of cells through a workbook (2002)

    Devious!!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging groups of cells through a workbook (2002)

    After working with the formula I've found that it's only looking at the first sheet. Does this have something to do with your comment about confirmation? Sorry if I'm being a little thick.

  9. #9
    Lounger
    Join Date
    Mar 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging groups of cells through a workbook (2002)

    Steve, I've been enlightened to the need to enter array formulas with ctrl-shift-enter. The formula is returning "value is not available to the formula or function" Could you see if I'm missing something? Thanks

  10. #10
    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 groups of cells through a workbook (2002)

    I can't open the attachment. It crashes excel.

    I get :
    excel.exe application error
    The instruction at "0x30314965" referenced memory at "0x00000008". The memory could not be "read".

    whenever I try to open it.

    Steve

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Averaging groups of cells through a workbook (2002)

    Chas,
    It doesn't work in my Excel 2002 either. Excel and 3-dimensional ranges don't always play well together and there are not many functions that will happily work with ranges across worksheets. You could add a defined range to each sheet and then have an array of cells on the summary sheet that returns the array of averages, then simply use the MIN function on that range of cells.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    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 groups of cells through a workbook (2002)

    You don't need named ranges. If you place the following formula in B1:
    =average(indirect("Sheet"&ROW(indirect("1:31"))&"! $A$1:$A$5"))

    Then highlight B1:b31, hit f2, then ctrl-shift-enter, the averages of each sheet will be displayed in B1:B31.

    Then Min(B1:B31) is the "minimum average".

    I don't understand why the array formula:
    =min(average(indirect("Sheet"&ROW(indirect("1:31") )&"!$A$1:$A$5")))
    which essentially does this, does not seem to work.

    I can get the array of averages, but when I add the min to it, I keep crashing excel.

    Steve

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Averaging groups of cells through a workbook (2002)

    Steve,
    You're right - I should have clarified that I mentioned named ranges simply because I usually use them - makes it easier when the cells referred to (inevitably) change.
    I confess I've given up trying to understand why some things will work in Excel with 3D ranges and others won't. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Averaging groups of cells through a workbook (2002)

    Hi Steve,

    I have the same experience you have under Excel 97 SR2 - the formula =AVERAGE(...) array-entered into a block of cells returns correct results, but =MIN(AVERAGE(...)) array-entered into a single cell crashes Excel 97. It doesn't crash Excel 2002 SP2, but returns #N/A instead - not very helpful either, but a slight improvement. Perhaps it'll work correctly under Excel 2007...

  15. #15
    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 groups of cells through a workbook (2002)

    One of the odd parts of this is that the ARRAY is NOT really a 3D formula. It creates an array of the averages from each of the 31 sheets and then finds the MIN from that array.

    I guess, just something to chalk up to a new undiscovered feature of excel. Enter this formula and crash excel.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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