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

19 Department	Welcome	Sales	Cash	Attitude	General	Total
20 Camping	100.00%	71.00%	0.00%	63.64%	70.00%	64.27%
21 Nautical	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
23 Winter	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
24 Sacs	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
25 Car Racks	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
26 Weighted	17.00%	37.00%	14.00%	22.00%	10.00%

<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

 

[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% ...