Weighted Average Problem?

Hi Forum,

I have posted this in Excel New Users/Basic Forum. So far did not receive a response. As I have to deliver the results of this tomorrow early morning to my boss, I am posting this in “Excel Formulas & Functions” forum too. Sorry about that.

In the attached, I have 3 source tables, called Table 1, Table 2 and Table 3.

Problem:
Using these 3 tables, I have to calculate the summary table indicated in multi-colours to answer below two questions:

1). When intervention = 1, then what is the weighted avg of “Bad Rate #” across three source tables ? 0.18%

2). When “Bad Rate #” across three source tables is highest for intervention 1, then what is the value of “# of people” ? 75

Question:
I have tried below sumproduct function with an added “if” phrase like below to give a condition to restrict for a chosen intervention at a time but gives an error

=sumproductif(B3:O3,B7:O7,J15)/SUM(B3:O3)

I have calculated the weighted avg in colum “L” but it is very clumsy to manually restricting for interventions given the large number of source tables.

Could anyone help me how to create a look up function to answer the above 2questions.
Thank you for your expertise.

Mirisage

Hi Max, Singapore,
Your code is nothing but great!
I will try to learn the logic later. Now I focus on deleivery of output.
Thanks again!
Mirisage

welcome, glad it worked out ok