AVERAGE Across Multiple Worksheets

[rappleby]

I need to average a set of figures across multiple worksheets.
In searching the net, I found a way to do this:
=AVERAGE('Sheet1:Sheet10'!K5)

This will average the contents of the K5 cell across the consecutive 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).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. In my searching, 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),"'")

yielding text in the cell of '1998:2012'

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

and if I try to reference the cell containing the string, Excel opens an "Update File" dialog box asking me to update the reference??

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

To any and all responders - Thank You!

 

[drsarao]

Welcome to MrExcel.
Excel functions donot take kindly to builtup string addresses. You need to make it understandable by using INDIRECT().
Your generated address can become functional thus:
=AVERAGE(INDIRECT(CONCATENATE("'","1998",":",MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),"'")))

Or you can use a more flexible and robust ADDRESS() function:
=AVERAGE(INDIRECT(ADDRESS(5,11,,,"1998:" & MID(CELL("filename"),FIND("]",CELL("filename"))+1,255))))

Lookup both functions in Excel help to undrerstand better.

 

[Aladin Akyurek]

Insert two new sheets and name them First and Last. Then put all the relevant sheets between First and Last, also any additional sheet you make.

Now invoke:

=AVERAGE(First:Last!K5)

 

[rappleby]

Welcome to MrExcel.
Excel functions donot take kindly to builtup string addresses. You need to make it understandable by using INDIRECT().
Your generated address can become functional thus:
=AVERAGE(INDIRECT(CONCATENATE("'","1998",":",MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),"'")))

Or you can use a more flexible and robust ADDRESS() function:
=AVERAGE(INDIRECT(ADDRESS(5,11,,,"1998:" & MID(CELL("filename"),FIND("]",CELL("filename"))+1,255))))

Lookup both functions in Excel help to undrerstand better.

Thank you for responding. I've tried to understand the syntax and contents of these functions, but I can't seem to figure out how to get the result that I want. Here is what the column should look like in the 2011 sheet:

<TABLE style="WIDTH: 164pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=218><COLGROUP><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 3986" width=218><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 164pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20 width=218>=AVERAGE('1998:2011'!B7)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20>=AVERAGE('1998:2011'!B8)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20>=AVERAGE('1998:2011'!B9)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20>=AVERAGE('1998:2011'!B10)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20>=AVERAGE('1998:2011'!B11)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20>=AVERAGE('1998:2011'!B12)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20>=AVERAGE('1998:2011'!B13)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20>=AVERAGE('1998:2011'!B14)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20>=AVERAGE('1998:2011'!B15)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20>=AVERAGE('1998:2011'!B16)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20>=AVERAGE('1998:2011'!B17)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20>=AVERAGE('1998:2011'!B18)</TD></TR></TBODY></TABLE>

In the 2010 sheet it would be 1998:2010, in the 2009 it would be 1998:2009, etc.

I've used the MID function to generate the name of the sheet in the cell A1 but I just can't seem to figure out how to insert it into the AVERAGE formula???

 

[drsarao]

presuming you have put the worksheet name in A1 in all sheets, try this

=AVERAGE(INDIRECT("'1998:" & $A$1 & "'$B" & Row()))

 

[rappleby]

presuming you have put the worksheet name in A1 in all sheets, try this

=AVERAGE(INDIRECT("'1998:" & $A$1 & "'$B" & Row()))

Sorry, but that didn't work.

Thanks for sticking with this and trying to come up with a solution !

Yes, the name of the worksheet is in A1 arrived at with the formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

I also tried modifying your suggested formula to:

=AVERAGE(INDIRECT("'1998:"&INDIRECT("A1")&"'$B"&ROW()))

and

=AVERAGE(INDIRECT("'1998:"&TEXT($A$1,)&"'$B"&ROW()))

but in all cases, I get a #REF! error.

 

[drsarao]

I also tried and getting same #REF.
Maybe INDIRECT() does not work on multiple sheet references.
Aladin please throw some light here.

 

[Aladin Akyurek]

Sorry, but that didn't work.

Thanks for sticking with this and trying to come up with a solution !

Yes, the name of the worksheet is in A1 arrived at with the formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

I also tried modifying your suggested formula to:

=AVERAGE(INDIRECT("'1998:"&INDIRECT("A1")&"'$B"&ROW()))

and

=AVERAGE(INDIRECT("'1998:"&TEXT($A$1,)&"'$B"&ROW()))

but in all cases, I get a #REF! error.

I also tried and getting same #REF.
Maybe INDIRECT() does not work on multiple sheet references.
Aladin please throw some light here.

OK. What follows would be a possible option...

Enter in A1 in every sheet:

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

Invoke the following formula for averaging in the current sheet...

Control+shift+enter, not just enter, and copy down:

=AVERAGE(N(INDIRECT("'"&ROW(INDIRECT("1998:"&$A$1))&"'!"&CELL("address",B7))))

You could substitute the formula expression from A1 for A1 in the foregoing formula if so desired, but that would cause unnecessary repetitive calculations.

Hope this helps.

 

[rappleby]

OK. What follows would be a possible option...

Enter in A1 in every sheet:

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

This part worked and still provides the name of the worksheet in the cell; however, in looking up the REPLACE function, I can only surmise that you used REPLACE instead of MID to make sure there were no extraneous characters in the cell. Yes?

I don't understand what you mean by "Invoke" or when, where or how you want me to use Ctrl+Shift+Enter

We are getting closer - we've got a number in the cell instead of an error, but something isn't evaluating properly:

Using the worksheet for 2000 as a test, I replaced the old manual formula =AVERAGE('1998:2000'!B7) in cell D7 with the new formula using "copy and paste" - the figure for the average changed from $5,863,14 to $4,909.97. ($4,909.97 (contained in cell B7 on the 1998 sheet) is the January sales figure for the first year of operation).

I looked up the Ctrl+Shift+Enter and found that it is used to enter an array formula, so I manually typed the formula in the cell and then pressed Ctrl+Shift+Enter. This time the numbers remained the same. I copied and pasted the formula (which now had braces around it) in the FEB to DEC (D8:D18) range and all seems fine!!!

One last thing, I have modified the template page AVERAGE formula so that it doesn't show an average for the month until the monthly sales are entered in the B column =IF(B7<>0,AVERAGE('1998:2011'!B7),0). I've pasted in the changes so that it is now:
{=IF(B7<>0,AVERAGE(N(INDIRECT("'"&ROW(INDIRECT("1998:"&$A$1))&"'!"&CELL("address",B7)))),0)}
(the braces were added by excel after pressing Ctrl+Shift+Enter).

Seems to work correctly. Can you confirm that the syntax is correct?

Brilliant solution. Thank you both so very much for helping me out here.

So (because there are embedded graphs on the sheet), now, I can simply make a copy of the Template to a new workbook, change the name to the current year, copy it back to the Monthly Sales and Income workbook and VIOLA - EUREKA - Beautiful - Cool - Thanks!!!

 

[Aladin Akyurek]

This part worked and still provides the name of the worksheet in the cell; however, in looking up the REPLACE function, I can only surmise that you used REPLACE instead of MID to make sure there were no extraneous characters in the cell. Yes?

The number of functions calls are less, therefore more efficient with the REPLACE formula.

I don't understand what you mean by "Invoke" or when, where or how you want me to use Ctrl+Shift+Enter

We are getting closer - we've got a number in the cell instead of an error, but something isn't evaluating properly:

Using the worksheet for 2000 as a test, I replaced the old manual formula =AVERAGE('1998:2000'!B7) in cell D7 with the new formula using "copy and paste" - the figure for the average changed from $5,863,14 to $4,909.97. ($4,909.97 (contained in cell B7 on the 1998 sheet) is the January sales figure for the first year of operation).

I looked up the Ctrl+Shift+Enter and found that it is used to enter an array formula, so I manually typed the formula in the cell and then pressed Ctrl+Shift+Enter. This time the numbers remained the same. I copied and pasted the formula (which now had braces around it) in the FEB to DEC (D8:D18) range and all seems fine!!!

Right. The formula requires control+shift+enter and more importantly the N call for dereferencing. This just for your information.

One last thing, I have modified the template page AVERAGE formula so that it doesn't show an average for the month until the monthly sales are entered in the B column =IF(B7<>0,AVERAGE('1998:2011'!B7),0). I've pasted in the changes so that it is now:
{=IF(B7<>0,AVERAGE(N(INDIRECT("'"&ROW(INDIRECT("1998:"&$A$1))&"'!"&CELL("address",B7)))),0)}
(the braces were added by excel after pressing Ctrl+Shift+Enter).

Seems to work correctly. Can you confirm that the syntax is correct?

B7 in IF would just refer to B7 in the current sheet where the formula is, not B7 cells of the other sheets...

Brilliant solution. Thank you both so very much for helping me out here.

So (because there are embedded graphs on the sheet), now, I can simply make a copy of the Template to a new workbook, change the name to the current year, copy it back to the Monthly Sales and Income workbook and VIOLA - EUREKA - Beautiful - Cool - Thanks!!!

You are welcome. Thanks for the feedback.