Page 1 of 2 12 LastLast
Results 1 to 10 of 14

AVERAGE Across Multiple Worksheets

This is a discussion on AVERAGE Across Multiple Worksheets within the Excel Questions forums, part of the Question Forums category; I need to average a set of figures across multiple worksheets. In searching the net, I found a way to ...

  1. #1
    New Member
    Join Date
    Sep 2011
    Posts
    6

    Default AVERAGE Across Multiple Worksheets

    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!

  2. #2
    Board Regular drsarao's Avatar
    Join Date
    Sep 2009
    Location
    Delhi, India
    Posts
    1,105

    Default Re: AVERAGE Across Multiple Worksheets

    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.
    Excel 2007 Windows 7

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default Re: AVERAGE Across Multiple Worksheets

    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)
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    New Member
    Join Date
    Sep 2011
    Posts
    6

    Default Re: AVERAGE Across Multiple Worksheets

    Quote Originally Posted by drsarao View Post
    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:

    =AVERAGE('1998:2011'!B7)
    =AVERAGE('1998:2011'!B8)
    =AVERAGE('1998:2011'!B9)
    =AVERAGE('1998:2011'!B10)
    =AVERAGE('1998:2011'!B11)
    =AVERAGE('1998:2011'!B12)
    =AVERAGE('1998:2011'!B13)
    =AVERAGE('1998:2011'!B14)
    =AVERAGE('1998:2011'!B15)
    =AVERAGE('1998:2011'!B16)
    =AVERAGE('1998:2011'!B17)
    =AVERAGE('1998:2011'!B18)


    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???

  5. #5
    Board Regular drsarao's Avatar
    Join Date
    Sep 2009
    Location
    Delhi, India
    Posts
    1,105

    Default Re: AVERAGE Across Multiple Worksheets

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

    =AVERAGE(INDIRECT("'1998:" & $A$1 & "'$B" & Row()))
    Excel 2007 Windows 7

  6. #6
    New Member
    Join Date
    Sep 2011
    Posts
    6

    Default Re: AVERAGE Across Multiple Worksheets

    Quote Originally Posted by drsarao View Post
    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.
    Last edited by rappleby; Sep 25th, 2011 at 02:05 PM.

  7. #7
    Board Regular drsarao's Avatar
    Join Date
    Sep 2009
    Location
    Delhi, India
    Posts
    1,105

    Default Re: AVERAGE Across Multiple Worksheets

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

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default Re: AVERAGE Across Multiple Worksheets

    Quote Originally Posted by rappleby View Post
    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.
    Quote Originally Posted by drsarao View Post
    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.
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    Sep 2011
    Posts
    6

    Default Re: AVERAGE Across Multiple Worksheets

    Quote Originally Posted by Aladin Akyurek View Post
    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?



    [QUOTE=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.[/QUOTE]

    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!!!

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default Re: AVERAGE Across Multiple Worksheets

    Quote Originally Posted by rappleby View Post
    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.
    Assuming too much and qualifying too much are two faces of the same problem.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com