Averageif across multiple sheets with variable content

Chantilly

New Member
Joined
Jul 15, 2019
Messages
9
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! :)
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

AG139
AJ540
AA728
AD15
AA294
AA854
AA565
AA371
AA595
AA919
AA489
AA163
AA613
AA150
AA372

<tbody>
</tbody>

Week 2 - Jan 13

AA693
AH180
AJ454
AA547
AA276
AA794
AA986
AA477
AE563
AH759
AB961
AA367
AC329
AA577
AC221

<tbody>
</tbody>

Week 3 - Jan 20

AI927
AA105
AJ85
AA741
AB796
AD272
AG127
AB819
AF186
AA3
AF15
AI851
AA106
AH841
AA117

<tbody>
</tbody>

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

New Member
Joined
Jul 15, 2019
Messages
9
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

New Member
Joined
Jul 15, 2019
Messages
9
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517

ADVERTISEMENT

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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Apologies. Posted without seeing your previous. Thanks. Will get back to you with a solution soon.
 

Chantilly

New Member
Joined
Jul 15, 2019
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

New Member
Joined
Jul 15, 2019
Messages
9
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,431
Messages
5,511,300
Members
408,839
Latest member
ayyazahmad89

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top