Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating weighted averages
I'm having issues with weighted averages for some data I'm working with. Here
is an example: I have 200,000 records Of which 8,000 have a value ranging from 3-15 The sum of those values is 40,000 The straight average is 5, based on the 8,000 records with values Another sample 1,000,000 records Of which 150,000 have a value ranging from 3-15 The sum of those values is 675,000 The straight average is 4.5, based on the 150,000 records with values My question is how to make this averages weighted? The one with 200,000 records has a higher average than the 1,000,000 record sample, but I need to reflect that based on the volume differences. Any ideas? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating weighted averages
Hello rgl. Hete is a link that may help you. http://support.microsoft.com/?kbid=214049 Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=513770 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating weighted averages
This depends on how your data is laid out.
I'm assuming you have the two sets of records on different worksheets. I'm also assuming from what you said that there is a data field in column 'X' that contains the value you want to average and that the other records have a null or text value in that field. If you have worksheets WS1 and WS2 with 200,000 and 1,000,000 respectively, then you can take a direct average of both ranges : =average('WS1'!X1:X200000','WS2'!X1:X1000000) This will exclude empty cells and text. HTH -Rayo "rgl" wrote: I'm having issues with weighted averages for some data I'm working with. Here is an example: I have 200,000 records Of which 8,000 have a value ranging from 3-15 The sum of those values is 40,000 The straight average is 5, based on the 8,000 records with values Another sample 1,000,000 records Of which 150,000 have a value ranging from 3-15 The sum of those values is 675,000 The straight average is 4.5, based on the 150,000 records with values My question is how to make this averages weighted? The one with 200,000 records has a higher average than the 1,000,000 record sample, but I need to reflect that based on the volume differences. Any ideas? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating weighted averages
Here is how my data is laid out.
Product Volume Records w/ Values Sum of Values Avg Weighted Avg A 97,194 7,581 37600.42 4.96 B 494,243 270,686 1112406.72 4.11 C 509,296 12,093 57309.7 4.74 D 465,709 27,863 124111.05 4.45 E 867,972 104,506 489445.01 4.68 F 304,290 99,523 537660.81 5.40 G 504,195 71,224 329201.34 4.62 H 174,777 8,146 34211.58 4.20 I 605,050 41,648 202445.57 4.86 J 971,978 88,718 419331.73 4.73 Total 4,994,704 731,988 3,343,724 4.57 Instead of the regular avg. I calculated, I need a weighted average to reflect the volume differences in the different products. Hope that helps. Let me know. Thanks. RGL "Rayo K" wrote: This depends on how your data is laid out. I'm assuming you have the two sets of records on different worksheets. I'm also assuming from what you said that there is a data field in column 'X' that contains the value you want to average and that the other records have a null or text value in that field. If you have worksheets WS1 and WS2 with 200,000 and 1,000,000 respectively, then you can take a direct average of both ranges : =average('WS1'!X1:X200000','WS2'!X1:X1000000) This will exclude empty cells and text. HTH -Rayo "rgl" wrote: I'm having issues with weighted averages for some data I'm working with. Here is an example: I have 200,000 records Of which 8,000 have a value ranging from 3-15 The sum of those values is 40,000 The straight average is 5, based on the 8,000 records with values Another sample 1,000,000 records Of which 150,000 have a value ranging from 3-15 The sum of those values is 675,000 The straight average is 4.5, based on the 150,000 records with values My question is how to make this averages weighted? The one with 200,000 records has a higher average than the 1,000,000 record sample, but I need to reflect that based on the volume differences. Any ideas? Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating weighted averages
The formula for weighted average in this case is:
=(200000*5+1000000*4.5)/(200000+1000000) HTH Kostis Vezerides |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating weighted averages
How did you derive that formula?
if i have these values, where would i plug them into this equation you provided? volume records with values Sum of values avg. 97,194 7,581 37600.42 4.96 "vezerid" wrote: The formula for weighted average in this case is: =(200000*5+1000000*4.5)/(200000+1000000) HTH Kostis Vezerides |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating weighted averages
Hi Leith,
I saw that link and checked out the examples they gave, but I still don't know how to apply it to my problem. Do you know what formulas/equations are needed to figure it out? Thanks. RGL "Leith Ross" wrote: Hello rgl. Hete is a link that may help you. http://support.microsoft.com/?kbid=214049 Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=513770 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weighted Averages in Excel | Excel Discussion (Misc queries) | |||
Calculating averages excluding outliers...a question | Excel Worksheet Functions | |||
Calculating basic averages...a question | Excel Worksheet Functions | |||
calculating averages | Excel Discussion (Misc queries) | |||
creating intervals and calculating averages | Excel Discussion (Misc queries) |