Weighted Averages

[PhilF]

Can anyone enlighten me how to calc a weighted averages of a column of
averages? I have calculated the average discount of product sales (ie, each
sale has a different discount), and i want to get a weighted average of of
the sum of them. I'm pretty much stumped.

Thanks.

 

[Sheikh Saadi]

Sppose you have Product Price in Column B and Qty in Column C, use this
formula to calculate weighted avg:

= SUMPRODUCT(B2:B10, C2:C10) / SUM(C2:C10)

Hope this will help...

 

[PhilF]

Thank you for trying to help--but I realize I may not have explained my
problem in enough detail. I have sales of a group of products, each with a
different list price, different number of units sold, different net
receipts for each product, and hence a different discount. I calculated a
discount for each product by dividing the total proceeds of each product's
sales by the number of units, then dividing the result by the list price of
the product, which gave me a percentage discount. What I am now attempting
to find is the average discount for the group, weighted by the amount of
sales. Using the Average function on the column of average discounts gives
me an average of the averages, not a weighted average. I'm not sure your
reply is valid because I inadequately explained my problem. Thanks again for
your help.

Phil F.

 

[Ashish Mathur]

Hi,

Sum up the discount column and divide by the total of net receipts

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Sheikh Saadi]

I am really sorry but still I am unclear. Could you plz paste some sample
data along with the column names and the formulae used? This would help to
understand your problem more clearly.

 

[PhilF]

Here's a sample. The average discount is arrived at by dividing 2009$ by 2009
units, then dividing the result by the list price and subtracting 1. Hope
this makes it clear. I'm trying to find the average discount of the entire
range.

ITEM List 2009 $ 2009 Units Avg Disc
2226-6 89.95 $9,920 182 -39%
2228-0 94.95 $3,900 62 -34%
1673-9 57.95 $669 12 -4%
1806-1 83.95 $1,329 22 -28%
1675-0 72.95 $163 3 -26%
1573-8 72.95 $378 8 -35%
1723-4 99.95 $361 5 -28%
1669-6 87.95 $237 4 -33%
1452-9 110.95 $559 7 -28%
1752-8 93.95 $122 2 -35%
1323-9 94.95 $7,256 117 -35%
Thanks.

 

[Sheikh Saadi]

if i'm understanding correctly, you need to:

= SUM(Discount Range) / COUNT(Discount Range)

See if you are luking for this...

 

[PhilF]

Yes--that appears to be it! Thank you so much.

Phil