Calculating weighted averages

[rgl]

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.

[Leith Ross]

Hello rgl.

Hete is a link that may help you.
http://support.microsoft.com/?kbid=214049

Sincerely,
Leith Ross

[Rayo K]

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

[rgl]

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

[vezerid]

The formula for weighted average in this case is:
=(200000*5+1000000*4.5)/(200000+1000000)

HTH
Kostis Vezerides

[rgl]

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

[rgl]

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