Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weighted Averages for Rankings | Excel Worksheet Functions | |||
Weighted Averages? | Excel Worksheet Functions | |||
weighted averages in a pivot table - how? | Excel Worksheet Functions | |||
Calculating weighted averages | Excel Discussion (Misc queries) | |||
Weighted Averages in Excel | Excel Discussion (Misc queries) |