Weighted average using two columns of percentages

[Bluesky50]

I am trying to figure out the right use of the sumproduct funtion in a specific application. I have three categories of products (A, B, C). When we analyzed a client's cost structure relative to ours to purchase each of these categories, we found a spread between our two cost structures of 45.1% for A, 16.9% for B, and 52.8 for C. Further, each of these product categories can be weighted as as a percentage of the client's total cost; A=62.5%, B=18.8%, C=18.8%.

<table x:str="" style="border-collapse: collapse; width: 166pt;" width="220" border="0" cellpadding="0" cellspacing="0"><col style="width: 65pt;" width="86"> <col style="width: 53pt;" width="70"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 65pt;" width="86" height="17">Product</td> <td class="xl24" style="border-left: medium none; width: 53pt;" width="70">Spread</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64">Weight</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">A</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.45110569847593951" align="right">45.1%</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.625" align="right">62.5%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">B</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.16875750009888385" align="right">16.9%</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.1875" align="right">18.8%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">C</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.52796418859472005" align="right">52.8%</td> <td class="xl25" style="border-top: medium none; border-left: medium none;" x:num="0.1875" align="right">18.8%</td> </tr> </tbody></table>
Therefore, I am trying to calculate the average weighted spread between our two cost structures (as a percentage) using Excel's sumproduct. Should I use sumproduct(b1:b3, c1:c3) or sumproduct (b1:b3,c1:c3/b1:b3)?

Thank you,

Dave

 

[Andrew Poulsom]

I would have thought:

=SUMPRODUCT(B1:B3, C1:C3)

which returns 41.29% and looks correct (your other formula returns 1.001).

 

[Bluesky50]

Thank you! My mistake. I meant inquire which was appropriate a) sumproduct(b1:b3,c1:c3) or b) sumproduct(B1:B3,c1:c3)/sum(b1:b3) for this application.

 

[Andrew Poulsom]

The formula I posted is correct. Make all the percentages in B1:B3 the same and you will see why.