Feb 10 2022 10:40 AM
Hello,
I am struggling with writing a formula to take the average of the product of two columns across 15+ identical worksheets. The two columns have numerical data, and the constraint is that I can't add another column on the worksheets to take the product before averaging it.
Please see example Excel Sheets attached.
Thanks,
Shawna
Feb 12 2022 09:16 AM
Why not append the tables from several worksheets with Power Query? After this it's simple to calculate the sumproduct and the average. Writing VBA code for this would be much more difficult and setting up a formula with the INDIRECT function for 15+ worksheets to achieve this result, i wonder if this is even possible.
Feb 16 2022 06:25 AM
Feb 16 2022 07:09 AM
Does the example in sheet "Tabelle1" in the attached file show what you want to do? If so i would append all tables with Power Query and then apply the formulas in one resulting worksheet.
Feb 16 2022 08:47 AM
It didn't quite match the idea, I edited it to give you a better picture of the problem.
Feb 16 2022 10:34 AM
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.
Feb 16 2022 12:08 PM
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.
Feb 16 2022 05:26 PM
SolutionIn 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.
Feb 17 2022 04:23 AM
Feb 16 2022 05:26 PM
SolutionIn 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.