Forum Discussion
The average of the product of two columns across several worksheets
In my understanding you have solved the task yourself. Your solution is much easier than Power Query and if i may i would suggest to enter an additional column in all of your 54 sheets.
All your sheets have the same layout which means that you can easily add the additional column.
You can select all the 54 Assembly and Machining sheets by activating the first (Assembly) sheet and then holding down the ctrl key and select the remaining sheets with mouseclicks.
Then enter formula
=C3*D3
in the first sheet and copy the formula down.
Then hold the ctrl key down again and deselect the other sheets with mouseclicks.
Now in all sheets the formulas are entered and the product column is added.
It didn't quite match the idea, I edited it to give you a better picture of the problem.
I removed the merged cells A2 and B2 in all tables in order to convert the tables Assembly 1 - 3 and Machining 1 - 3 into dynamic tables and to append them with Power Query.
In the summary table the AVERAGEIF and AVERAGEIFS formulas calculate the results you might be looking for.
That is very close to accomplishing the goal, but I was trying to avoid adding the extra column in the sheets if at all possible. If I have to add an extra column and hide it, then I would probably just make that the product column and use an AVG function (i.e. =AVERAGE('Assembly 1:Machining 3'!E3)) where E3 is the product column. I have 54 sheets total, so it may be a bit cumbersome, but if there is no alternative I will have to use it.
In my understanding you have solved the task yourself. Your solution is much easier than Power Query and if i may i would suggest to enter an additional column in all of your 54 sheets.
All your sheets have the same layout which means that you can easily add the additional column.
You can select all the 54 Assembly and Machining sheets by activating the first (Assembly) sheet and then holding down the ctrl key and select the remaining sheets with mouseclicks.
Then enter formula
=C3*D3
in the first sheet and copy the formula down.
Then hold the ctrl key down again and deselect the other sheets with mouseclicks.
Now in all sheets the formulas are entered and the product column is added.
- That works great! I thought I would have to do it for each individual sheet, but I just tried it and I can make all of the edits on a single sheet and it copies over to the others. Thanks!