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