Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default 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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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.


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default 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.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 27
Default 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.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Weighted Averages for Rankings katie Excel Worksheet Functions 1 April 8th 09 05:33 PM
Weighted Averages? Kevin Excel Worksheet Functions 5 April 2nd 09 02:55 PM
weighted averages in a pivot table - how? Vic Excel Worksheet Functions 0 April 22nd 08 07:07 PM
Calculating weighted averages rgl Excel Discussion (Misc queries) 6 February 22nd 06 07:15 PM
Weighted Averages in Excel Karl Excel Discussion (Misc queries) 2 February 9th 06 01:36 PM


All times are GMT +1. The time now is 12:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"