How to calculate weighted average with multiple criteria

[dahl07]

Hello,

I have figured out how to calculate the total weighted average in my data sheet with this formula:

=SUMPRODUCT((DATA!AA285:AA20000)*(DATA!N285:N20000))/(SUM(DATA!N285:N20000))

Now I need to figure out, how I can calculate this with two criterias;
1) DATA!AH285:AH20000=Distrikter!C3
2) DATA!H285:H20000=Distrikter!D2

I am not sure what to do from here - maybe IF or another SUMPRODUCT.

Thank you for considering my request, I have used several hours on this now...

 

[ctrl s]

@ctrl s

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Group[/TD]
[TD]Score[/TD]
[TD]Percentage[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

8769​

[/TD]
[TD]

94%​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

329729.2​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

986757​

[/TD]
[TD]

96%​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

278044​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

685​

[/TD]
[TD]

100%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

765874​

[/TD]
[TD]

87%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

2​

[/TD]
[TD]

7644​

[/TD]
[TD]

90%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

2​

[/TD]
[TD]

76875​

[/TD]
[TD]

90%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

3​

[/TD]
[TD]

7654​

[/TD]
[TD]

2%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

3​

[/TD]
[TD]

76546​

[/TD]
[TD]

40%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

3​

[/TD]
[TD]

76453​

[/TD]
[TD]

89%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

4​

[/TD]
[TD]

8765​

[/TD]
[TD]

65%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

4​

[/TD]
[TD]

876554​

[/TD]
[TD]

98%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In F2 enter and copy down:

=SUMPRODUCT(($A$2:$A$12=E2)+0,$B$2:$B$12,$C$2:$C$12)/SUMIFS($C$2:$C$12,$A$2:$A$12,E2)

Thanks so much for the swift reply! This definitely get me closer but I'm looking for the weighted average in a percentage (I hope that makes sense).

 

[Aladin Akyurek]

Thanks so much for the swift reply! This definitely get me closer but I'm looking for the weighted average in a percentage (I hope that makes sense).

It would be so much simpler if you would have provided the manually calculated expected values...

=SUMPRODUCT(($A$2:$A$12=E2)+0,$B$2:$B$12,$C$2:$C$12)/SUMIFS($B$2:$B$12,$A$2:$A$12,E2)

 

[ctrl s]

It would be so much simpler if you would have provided the manually calculated expected values...

=SUMPRODUCT(($A$2:$A$12=E2)+0,$B$2:$B$12,$C$2:$C$12)/SUMIFS($B$2:$B$12,$A$2:$A$12,E2)

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[/TR]
[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Group[/TD]
[TD]Score[/TD]
[TD]Percentage[/TD]
[TD][/TD]
[TD]Group[/TD]
[TD]WA%[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

8769​

[/TD]
[TD]

94%​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

98%​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

986757​

[/TD]
[TD]

96%​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

83%​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

685​

[/TD]
[TD]

100%​

[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]84%[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

765874​

[/TD]
[TD]

87%​

[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

2​

[/TD]
[TD]

7644​

[/TD]
[TD]

90%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

2​

[/TD]
[TD]

76875​

[/TD]
[TD]

90%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

3​

[/TD]
[TD]

7654​

[/TD]
[TD]

2%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

3​

[/TD]
[TD]

76546​

[/TD]
[TD]

40%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

3​

[/TD]
[TD]

76453​

[/TD]
[TD]

89%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

4​

[/TD]
[TD]

8765​

[/TD]
[TD]

65%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

4​

[/TD]
[TD]

876554​

[/TD]
[TD]

98%​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My apologies Aladin. This is what I'm looking for. I need the formula in column F to refer to the specific group (1,2,3 or 4) and provide the weight average in % of that particular group instead of providing the total sum.

Thanks again!

 

[Aladin Akyurek]

...

My apologies Aladin. This is what I'm looking for. I need the formula in column F to refer to the specific group (1,2,3 or 4) and provide the weight average in % of that particular group instead of providing the total sum.

Thanks again!

Are you saying that:

=SUMPRODUCT(($A$2:$A$12=E2)+0,$B$2:$B$12,$C$2:$C$12)/SUMIFS($B$2:$B$12,$A$2:$A$12,E2)

does not fit the job? If it doesn't, how do you get 98% for 1 instead of 95.96% this formula would calculate?