Averageif across multiple sheets with variable content

[Chantilly]

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!

 

[XOR LX]

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

[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64"]AG[/TD]
[TD="width: 64, align: right"]139[/TD]
[/TR]
[TR]
[TD]AJ[/TD]
[TD="align: right"]540[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]728[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]294[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]854[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]565[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]371[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]595[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]919[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]489[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]163[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]613[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]372[/TD]
[/TR]
</tbody>[/TABLE]

Week 2 - Jan 13

[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64"]AA[/TD]
[TD="width: 64, align: right"]693[/TD]
[/TR]
[TR]
[TD]AH[/TD]
[TD="align: right"]180[/TD]
[/TR]
[TR]
[TD]AJ[/TD]
[TD="align: right"]454[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]547[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]276[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]794[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]986[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]477[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD="align: right"]563[/TD]
[/TR]
[TR]
[TD]AH[/TD]
[TD="align: right"]759[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD="align: right"]961[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]367[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD="align: right"]329[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]577[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD="align: right"]221[/TD]
[/TR]
</tbody>[/TABLE]

Week 3 - Jan 20

[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64"]AI[/TD]
[TD="width: 64, align: right"]927[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD]AJ[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]741[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD="align: right"]796[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD="align: right"]272[/TD]
[/TR]
[TR]
[TD]AG[/TD]
[TD="align: right"]127[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD="align: right"]819[/TD]
[/TR]
[TR]
[TD]AF[/TD]
[TD="align: right"]186[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]AF[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]AI[/TD]
[TD="align: right"]851[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]106[/TD]
[/TR]
[TR]
[TD]AH[/TD]
[TD="align: right"]841[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD="align: right"]117[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Chantilly]

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?

 

[Chantilly]

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

 

[XOR LX]

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

 

[XOR LX]

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

 

[Chantilly]

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.

 

[XOR LX]

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).

 

[Chantilly]

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

 

[XOR LX]

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