AVERAGE Across Multiple Worksheets

rappleby

New member
Joined
Sep 22, 2011
Messages
1
Reaction score
0
Points
0
Hi Folks :)

In searching the net, I found a way to average cells across worksheets: =AVERAGE('Sheet1:Sheet10'!K5)

This will average the contents of the J5 cell across the range of worksheets between Sheet1 and Sheet10. I am using sales data on worksheets named for the year so my actual formula would be =AVERAGE('1998:2011'!K5). So, if manually entered the formula in the worksheet for 1998:2005, the average will be calculated for the years 1998 to 2005.

Works just fine if I manually enter the worksheet names, but I am creating a template from which I can create a new worksheet each year. I also found a function that will provide the name of the worksheet in a cell:

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

so I am looking for a way to put this function in the formula so that when I copy the template and name the new sheet "2012" the =AVERAGE formula will be =AVERAGE('1998:2012'!K5). I have been able to successfully CONCATENATE the text in a separate cell using

=CONCATENATE("'","1998",":",MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),"'")

but as soon as I try to substitute this in the AVERAGE function, I get standard AVERAGE error messages showing
=AVERAGE(value1, value2,value3,....

Does anybody know how I can use the MID function in the AVERAGE function so that I can automatically generate the correct formula when I create and name a new sheet from the template????

To any and all responders - Thank You! :)
 
Last edited:

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
One method might be to create empty "bookend" sheets, you can put one before the first sheet and after the last... or if the first is fixed at 1998, then just one for the last... so insert an empty sheet after 2011, call it something like "Last"..

You can then use formula: =AVERAGE('1998:Last'!K5)

or, if you put one in before 1998, called "First", then =AVERAGE('First:Last'!K5)

so now the average formula will include any sheet inserted between First and Last sheets.
 
Top