Calculate a running grand average from multiple worksheets

**rangercole** · 09-21-2009, 10:45 AM

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** · 09-21-2009, 10:51 AM

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.