Weighted average using SUMIF and/or SUMPRODUCT

M

MB51

My data table looks something like this:
Col A Col B Col C
Product Score Stops
X 98% 100
Y 97% 50
X 75% 150
Z 80% 25
X 99% 200

Totals:
X ?? 450 (using SUMIF for X)
Y ?? 50
Z ?? 25

I would like to get the weighted average by product (such as x) of column B
(score) based on column C (the weight of each row's score based on its % of
X's total stops).

The ?? in Totals should return the sum of
((98%*(100/450))+(75%*(150/450))+(99%*(200/450))) but I am looking for a more
versatile way to do this so when I add products/results in the future it will
include them in the weighted average.
 
V

vezerid

With ?? in K2, "X" in J2,

=SUMPRODUCT(($A$2:$A$6=J2)*$B$2:$B$6*$C$2:$C$6)/SUMPRODUCT(($A$2:$A
$6=J2)*$C$2:$C$6)

HTH
Kostis Vezerides
 
M

MB51

Thank you...it worked!

vezerid said:
With ?? in K2, "X" in J2,

=SUMPRODUCT(($A$2:$A$6=J2)*$B$2:$B$6*$C$2:$C$6)/SUMPRODUCT(($A$2:$A
$6=J2)*$C$2:$C$6)

HTH
Kostis Vezerides
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top