Averageif across multiple sheets with variable content
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Averageif across multiple sheets with variable content
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Location
    Calgary, AB, Canada
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Averageif across multiple sheets with variable content

    Hi there,

    I am trying to do an average of data on a summary sheet from multiple sheets, however, the data I need is coming from an already existing averageif formula on each sheet. I have set up the columns and rows to be exact on each sheet for the values needed. Each sheet will be labeled by their respective week, but also the date that starts the week, so I need to include the variable "*" after the week range so that it will include all the weeks. The data will also vary on each sheet, as every week is different.
    With this, want to do one formula that will encompass the data that is separated by weeks for the year (so it is future dated to include upcoming weeks that have not yet happened in the year) - not sure if this is possible?

    I will provide the below example to help understand my query:

    -Sheet 1 (labeled "Week 1 - Jan 6")
    -On this sheet, cells containing averageif rules from M1 - M10 (qualifying content) and N1 - N10 (values averaged)
    Cell M1 would be "AA"
    Cell M2 would be "AB"
    Cell M3 would be "AC" etc....

    Cell N1 would be (averageif value with data from table that includes criteria of AA) lets say for this example it's "12"
    Cell N2 would be "16" (using same averageif criteria, but for AB)
    Cell N3 would be #Div/0! (because AC code was not used that week and so does not have a value to average)

    - Sheet 2 (labeled "Week 2 - Jan 13")
    - Cells M1:M10 are exactly the same, but value returns in cells N1:N10 would be different according to that weeks codes and average numbers

    Sheets would continue accordingly for the rest of the year until Week 52.

    Averageif rule in each sheet pulls the data based on the code and then I would like an average over all the sheets for the year as I continue to input new weeks into the spreadsheet.

    This is the formula I tried for the first value of data (AA code), but failed...it just tells me to use an apostrophe:
    =averageif ('Week 1*':'Week 52*'!M1:N10,"AA",'Week 1*':'Week 52*'!N1:N10)

    Am using "*" to include variable of dates following the week label of each sheet.
    There are some #div/0 values on certain weeks where that code was not used, so I want to average all the ones that do have data.
    As this is future dated, weeks that have not happened yet will also not have any data, but I don't want to have to change the formula each week to include the average summary total....not sure if this is possible??

    I hope there is a way!!!!

    PLEASE HELP!

    Thanks in advance!

  2. #2
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Averageif across multiple sheets with variable content

    Hi,

    Are you sure that you should be generating your multi-sheet average via taking an 'average of averages' (a dubious and risky measure, in general)?

    This will only give correct results if the number of occurrences of each criteria ("AA", etc.) is precisely the same within all sheets. Otherwise, the average should be taken from the actual data, not the already generated averages.

    For example, imagine your raw data for each of the first three sheets was this:

    Week 1 - Jan 6

    AG 139
    AJ 540
    AA 728
    AD 15
    AA 294
    AA 854
    AA 565
    AA 371
    AA 595
    AA 919
    AA 489
    AA 163
    AA 613
    AA 150
    AA 372

    Week 2 - Jan 13

    AA 693
    AH 180
    AJ 454
    AA 547
    AA 276
    AA 794
    AA 986
    AA 477
    AE 563
    AH 759
    AB 961
    AA 367
    AC 329
    AA 577
    AC 221

    Week 3 - Jan 20

    AI 927
    AA 105
    AJ 85
    AA 741
    AB 796
    AD 272
    AG 127
    AB 819
    AF 186
    AA 3
    AF 15
    AI 851
    AA 106
    AH 841
    AA 117

    The corresponding individual sheet averages for "AA" (i.e. your cell N1 values) would be:

    Week 1 - Jan 6: 509.416666666667
    Week 2 - Jan 13: 589.625
    Week 3 - Jan 20: 214.4

    Your current proposed set-up for the multi-sheet average would then take the average of these three values, i.e. 437.813888888889.

    However, combining the data from the above three sheets would lead to a correct average for "AA" of 476.08.

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    New Member
    Join Date
    Jul 2019
    Location
    Calgary, AB, Canada
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Averageif across multiple sheets with variable content

    Xor Lx - Fair point
    I would still need assistance with the syntax of this rule, however, to generate across multiple sheets that will include empty data, as well as, future data.

    Any suggestions?

  4. #4
    New Member
    Join Date
    Jul 2019
    Location
    Calgary, AB, Canada
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Averageif across multiple sheets with variable content

    The raw data for the range on each sheet is A10:A50
    The criteria would be the individual codes, "AA" for this example.
    The average_range on each sheet is J10:J50

    Have tried using the following averageif with the raw data as above, and have also taken out the "*" variable to just include the sheets from Week 1 - Week 52, but still invalid - perhaps I'm missing apostrophes or something in the syntax?

    AVERAGEIF("'Week 1 - Jan 6'!A10:A50':'Week 52 - Dec 29'!A10:A50","AA","'Week 1 - Jan 6'!J10:J50':'Week 52 - Dec 29'!J10:J50'")

    TIA!!

  5. #5
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Averageif across multiple sheets with variable content

    Happy to help, though the intention of my previous post wasn't just to debate the correct method of averaging. The relevant point is that there's a big difference in a formula which calculates over a single value from multiple sheets and one which calculates over multiple values from multiple sheets.

    Bottom line is that I'd need to know the relevant ranges in each sheet in which the raw data is held.

    It would also be useful to know if the sheet names all follow the same format as those you give.

    Regards

  6. #6
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Averageif across multiple sheets with variable content

    Apologies. Posted without seeing your previous. Thanks. Will get back to you with a solution soon.

  7. #7
    New Member
    Join Date
    Jul 2019
    Location
    Calgary, AB, Canada
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Averageif across multiple sheets with variable content

    XOR LX - No worries...Understood and is a fair and valid point that I had not considered previously, so thank you for bringing it to my attention

    As for the sheet names, yes, they all follow the same format of "Week 1 - Jan 6, Week 2 - Jan 13, Week 3 - Jan 20" etc.

  8. #8
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Averageif across multiple sheets with variable content

    Try the following array formula**:

    =SUM(IFERROR(SUMIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("06/01/2019"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!A:A"),"AA",INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("06/01/2019"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!J:J")),0))/SUM(IFERROR(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("06/01/2019"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!A:A"),"AA"),0))

    Change the hard-coded reference to "AA" (in red) as desired (or, better still, put that value in an actual worksheet cell and reference that within the formula instead).

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Advanced Excel Techniques: http://excelxor.com/

  9. #9
    New Member
    Join Date
    Jul 2019
    Location
    Calgary, AB, Canada
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Averageif across multiple sheets with variable content

    First, I would like to thank you for taking the time to do this coding.
    Unfortunately, it did not work
    I needed to change the start date within the code to 12/30/2018, as the Week 1 started on the Sunday and needed to include Jan 1. And so, Week 1 was Dec 30
    I also added "*" to the "AA" since AA has various other alphanumeric behind it within the worksheets and so requires this variable (ie. AA5C900, AA6C700, etc).
    Not sure if these 2 changes caused it to not work, but are needed.

    Please see adjusted code below based on this and let me know if I have messed something up! (PS. I did the Ctrl+Shft+Enter for the array when entering)

    =SUM(IFERROR(SUMIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("30/12/2018"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!A:A"),"AA*",INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("30/12/2018"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!J:J")),0))/SUM(IFERROR(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("30/12/2018"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!A:A"),"AA*"),0))

  10. #10
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Averageif across multiple sheets with variable content

    Quote Originally Posted by Chantilly View Post
    I needed to change the start date within the code to 12/30/2018
    This makes me suspect you are using a US version of Excel, correct? If so, the dates in the formula I gave you need rewriting so that they are of the form mm/dd/yyyy, not dd/mm/yyyy.

    Regards

Some videos you may like

User Tag List

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
  •