The average of the product of two columns across several worksheets

[Shawna760]

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 

[#2]

@Shawna760 

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.

[#3]

@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.

[#4]

@Shawna760 

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.

[#5]

@OliverScheurich 

It didn't quite match the idea, I edited it to give you a better picture of the problem.

[#6]

@Shawna760 

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.

[#7]

@OliverScheurich 

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.

[#8 · Accepted solution]

@Shawna760 

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.

[#9]

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!