Results 1 to 2 of 2

Thread: AVERAGE Across Multiple Worksheets

  1. #1

    AVERAGE Across Multiple Worksheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 by rappleby; 2011-09-22 at 06:22 AM. Reason: clarity

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,369
    Articles
    0
    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.


Tags for this Thread

Posting Permissions

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