average from tables of varying sizes

[tontondavid10]

I have a workbook with over 100 worksheet. In each sheet I have a table, but each table is a slightly different size. I want to set cell A1 to be the average of every cell in the table. I suspect this will require a macro to ensure we only get as many cells as necessary. The first row and coloumn contains references, so I could look through the list to find if the reference is empty to find out when I need to end.

 

[Eric W]

Since AVERAGE ignores empty cells, you could just use

=AVERAGE(B2:Z100)

on each sheet, where you make the upper limit beyond the limits of your biggest table. If there is data below or to the right of your table, you can use

=AVERAGE(OFFSET(B2,0,0,COUNTA(A2:A20),COUNTA(B1:Z1)))

as long as there aren't any references in row 1 or column A beyond your table. If there are, we can use another way to find the end of the table.

 

[tontondavid10]

Thanks, the first method is quick and dirty enough for me.