AVERAGE Across Multiple Worksheets

rappleby

New Member
Joined
Sep 23, 2011
Messages
11
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!
 
I got some unusual results from the AVERAGE function, then I realized that because all the numbers were not yet entered for several of the years in the range, that the AVERAGE was incorrect because some of the months still contained "0".

I looked into a solution and found a method for producing an average by using SUM/(COUNT-COUNTIF) however; my attempts to replace AVERAGE with this function in the formula that you so graciously provided have been unsuccessful.

Could I impose upon your expertise and generosity one more time and request a formula which will generate the correct average even if there are zeros in some of the months of the array?

I would also like it to include the IF portion of the formula that I modified so that no average will be calculated for the current year until an amount is entered for that month in the current year. (You mentioned that the IF would only relate to the current sheet, and that is what I intended).

Thank you for considering my request.
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I got some unusual results from the AVERAGE function, then I realized that because all the numbers were not yet entered for several of the years in the range, that the AVERAGE was incorrect because some of the months still contained "0".

I looked into a solution and found a method for producing an average by using SUM/(COUNT-COUNTIF) however; my attempts to replace AVERAGE with this function in the formula that you so graciously provided have been unsuccessful.

Could I impose upon your expertise and generosity one more time and request a formula which will generate the correct average even if there are zeros in some of the months of the array?

I would also like it to include the IF portion of the formula that I modified so that no average will be calculated for the current year until an amount is entered for that month in the current year. (You mentioned that the IF would only relate to the current sheet, and that is what I intended).

Thank you for considering my request.

To exclude 0's from the average, try:
Rich (BB code):
=SUM(N(INDIRECT("'"&ROW(INDIRECT("1998:"&$A$1))&
   "'!"&CELL("address",B7))))/SUM(COUNTIF(INDIRECT("'"&
   ROW(INDIRECT("1998:"&$A$1))&"'!"&CELL("address",B7)),">0"))
 
Upvote 0
I am working on my company project to calculate average of daily production efficiency maintain in excel template. Each day create new file with the day and enters the data and calculate the efficiency using same template.
The excel file has six sheets call DAY-Line 1, DAY-Line 2, DAY-Line 3 and there are over 300 files with data on it. We need to calculate average efficiency in the past and bring it over to the template to update the efficiency automatically. I found this can be done writing formula as below
=AVERAGE('[Run Rate Chart - 20151201 Tuesday.xlsx]Day - Line 1:Day - Line 3'!$I$52:$I$84) this works fine for each file since I have 300 files it is very time consuming go to each files names to write this formula.
I got all the names writing formula if I can write some kind of concatenation formula I can get the average dynamically. I tried to write formula
=average (indirect("'"&"[Run Rate Chart - 20151201 Tuesday.xlsx]Day - Line 1:Day - Line 3'!$I$52:$I$84")) to first test whether I can use indirect function to use for the task, but unfortunately it gets #ref error.

Can any one help me to write correct formula for this situation.
Thank you
 
Upvote 0
I am working on my company project to calculate average of daily production efficiency maintain in excel template. Each day create new file with the day and enters the data and calculate the efficiency using same template.
The excel file has six sheets call DAY-Line 1, DAY-Line 2, DAY-Line 3 and there are over 300 files with data on it. We need to calculate average efficiency in the past and bring it over to the template to update the efficiency automatically. I found this can be done writing formula as below
=AVERAGE('[Run Rate Chart - 20151201 Tuesday.xlsx]Day - Line 1:Day - Line 3'!$I$52:$I$84) this works fine for each file since I have 300 files it is very time consuming go to each files names to write this formula.
I got all the names writing formula if I can write some kind of concatenation formula I can get the average dynamically. I tried to write formula
=average (indirect("'"&"[Run Rate Chart - 20151201 Tuesday.xlsx]Day - Line 1:Day - Line 3'!$I$52:$I$84")) to first test whether I can use indirect function to use for the task, but unfortunately it gets #ref error.

Can any one help me to write correct formula for this situation.
Thank you

INDIRECT does not work that way. Also, it does not work with closed books. It's probably better to sollicite for a VBA solution in a new thread you can start.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top