Forum Discussion
The average of the product of two columns across several worksheets
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
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.
8 Replies
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.
- OliverScheurich thank for the feedback, it may be more complicated than that... I need to take the product of the severity and probability for a specific row (hazard type) across all of the worksheets and then average that to get the average risk rating for that hazard. I tried the sumproduct function but I couldn't get it to work for this purpose. Would appending the tables make it possible? If it is not possible, I can work on an alternative solution, just wanted to exhaust all options before coming up with a more complicated work-around.
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.
