Weighted Averages
 

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.

Weighted Averages
 

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

--
Sheikh Saadi


"PhilF" wrote:

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.

Weighted Averages
 

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.

on "Sheikh Saadi" wrote:


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

--
Sheikh Saadi


"PhilF" wrote:

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.

Weighted Averages
 

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"PhilF" wrote in message
...
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.

on "Sheikh Saadi" wrote:


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

--
Sheikh Saadi


"PhilF" wrote:

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.

Weighted Averages
 

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.

--
Sheikh Saadi


"PhilF" wrote:

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.

on "Sheikh Saadi" wrote:


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

--
Sheikh Saadi


"PhilF" wrote:

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.

Weighted Averages
 

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" wrote:

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.

--
Sheikh Saadi


"PhilF" wrote:

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.

on "Sheikh Saadi" wrote:


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

--
Sheikh Saadi


"PhilF" wrote:

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.

Weighted Averages
 

if i'm understanding correctly, you need to:

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

See if you are luking for this...

--
Sheikh Saadi


"PhilF" wrote:

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" wrote:

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.

--
Sheikh Saadi


"PhilF" wrote:

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.

on "Sheikh Saadi" wrote:


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

--
Sheikh Saadi


"PhilF" wrote:

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.

Weighted Averages
 

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

Phil

"Sheikh Saadi" wrote:

if i'm understanding correctly, you need to:

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

See if you are luking for this...

--
Sheikh Saadi


"PhilF" wrote:

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" wrote:

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.

--
Sheikh Saadi


"PhilF" wrote:

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.

on "Sheikh Saadi" wrote:


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

--
Sheikh Saadi


"PhilF" wrote:

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.