Calculate a running grand average from multiple worksheets

[rangercole]

I have a workbook, in Excel 2007, that has many sheets. I usually add 4 to 5 sheets per week to this workbook. I want to get a calculate a running grand average of the averages from each sheet. The cell number is always the same on each sheet for the average. I know I could do a formula =(Sheet2!A46+nextsheet+….)\number of data points. This would have to be updated daily doing it this way. Is there any easier way?

[DonkeyOte]

If you set the Average calc on each sheet to return only a number if valid, eg:

A46: =IF(COUNT(A47:A100),AVERAGE(A47:A100),"")

Then add 2 sheets to your file to act as bookends

START - place before first sheet in final Avg
END - place after last sheet to be included in final Avg

Then on your summary sheet

=AVERAGE(START:END!A46)

When inserting new sheets just ensure they reside within START/END bookend sheets and they will automatically be included in the above calc.

[rangercole]

I have tried this and it worked great. Thanks for the tip and the quick response.