# Thread: Averaging groups of cells through a workbook (2002)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

Devious!!

8. ## 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. ## 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. ## 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. ## 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

12. ## 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. ## 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>

14. ## 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. ## 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 Last

#### Posting Permissions

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