Weighted average ignore 0%

[ToxikRick]

Hello,

For the first time, I am trying to calculate a weighted average. I think I got the weighted average formula correct: =SUM(B20*B25, C20*C25, D20*D25, E20*E25, F20*F25,)/SUM(B25:F25)

But we didn't get a score for the cash category, and need the 0% to not be counted. Is there something I add to this? or is there a better formula? My weighted average should work out to 73.08% for the camping line.

Thank you!



A B C D E F G[TABLE="width: 541"]
<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]19 Department[/TD]
[TD] Welcome[/TD]
[TD] Sales[/TD]
[TD] Cash[/TD]
[TD] Attitude [/TD]
[TD] General[/TD]
[TD] Total[/TD]
[/TR]
[TR]
[TD]20 Camping[/TD]
[TD] 100.00%[/TD]
[TD] 71.00% [/TD]
[TD] 0.00%[/TD]
[TD] 63.64%[/TD]
[TD] 70.00% [/TD]
[TD] 64.27%[/TD]
[/TR]
[TR]
[TD]21 Nautical [/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[/TR]
[TR]
[TD]23 Winter [/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[/TR]
[TR]
[TD]24 Sacs[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[/TR]
[TR]
[TD]25 Car Racks[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[TD] 0.00%[/TD]
[/TR]
[TR]
[TD]26 Weighted[/TD]
[TD] 17.00%[/TD]
[TD] 37.00%[/TD]
[TD] 14.00%[/TD]
[TD] 22.00%[/TD]
[TD] 10.00%[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

You could test following:

=SUMPRODUCT((B20:F20)*($B$25:$F$25))/SUMPRODUCT(--(B20:G20<>0),$B$25:$G$25)

Hope this will help

 

[ToxikRick]

Hello,

You could test following:

=SUMPRODUCT((B20:F20)*($B$25:$F$25))/SUMPRODUCT(--(B20:G20<>0),$B$25:$G$25)

Hope this will help

Thanks so much for the response! I appreciate your time

This gives me the sum without the 0%, but I need to have the average weighted to the A26-G-26 line. The % in B20 is worth 17% (C20 is worth 37%) of the overall average.

I hope this makes sense!

 

[ToxikRick]

never mind, I see you did do that. I changed the end part where it was referencing the G's. I think that was supposed to be F's. It stopped giving me a circular error after changing it. But I'm not getting the final number that I am supposed to be getting (73.08%).

I wish I could edit my post. Should have been rows 19-25 on there, not 19-26.

 

[James006]

Hello,

You can adjust both rows' numbers ... as needed in your actual workbook ...

[COLOR=#333333]=SUMPRODUCT((B20:F20)*($B$26:$F$26))/SUMPRODUCT(--(B20:F20<>0),$B$26:$F$26)[/COLOR]

By the way ... this formula tested ... produces 73.08% ...