# Thread: AVERAGE Across Multiple Worksheets

1. ## AVERAGE Across Multiple Worksheets

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!   Reply With Quote

2. 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.  Reply With Quote

average across worksheets, concatenate, multiple worksheets, sheet name in cell 