Weighted Average?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 03 Sep 2007 11:29 GMT

I have a file which contains four columns:

A) The 'unit' name
B) A forecast value from January 2004
C) The 'actual value from January 2007
D) The % difference between the forecast value and 'actual' value.

I want to find out the average % difference. I tried the following two
methods:

(1) I have summed all values in columns B and C and worked out the %
difference.
(2) I simply averaged the %'s in column D and I got a smaller figure.

This is because there are some extremely high value items (with large %
diffs) which obviously have a greater weighting on the summed totals in
method (1) and therefore have a bigger weighting on the overall % diff in
method (1).

The average % difference I am trying to calculate is simply an indicative
average which best represents a random sample of 'units', so I'm leaning
towards method (2) because it doesn't include the wieghting in method (1).
And besides, it is very unlikely that other collections of units would
include the high value items that are overly weighting the resulting % diff
in method (1).

Is this correct? Any help appreciated.....Rgds, Jason

Reply to this Message

Stan Brown - 03 Sep 2007 12:59 GMT

Mon, 3 Sep 2007 11:29:03 +0100 from Jay <dummy@dummy.dummy>:

> I have a file which contains four columns:
>
[quoted text clipped - 14 lines]
> method (1) and therefore have a bigger weighting on the overall % diff in
> method (1).

Method 1 is the only correct method mathematically.

If you feel that gives a wrong answer, look at any particular rows
that you feel are unrepresentative. Exclude them, and the you have
"weighted average excluding X, Y, and Z."

Signature

"First prove what you're saying, then whine about it."
-- /The People's Court/
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Reply to this Message

Opinicus - 03 Sep 2007 15:52 GMT

>I have a file which contains four columns:

> A) The 'unit' name
> B) A forecast value from January 2004
> C) The 'actual value from January 2007
> D) The % difference between the forecast value and 'actual' value.

> I want to find out the average % difference. I tried the following two
> methods:

> (1) I have summed all values in columns B and C and worked out the %
> difference.
> (2) I simply averaged the %'s in column D and I got a smaller figure.

> This is because there are some extremely high value items (with large %
> diffs) which obviously have a greater weighting on the summed totals in
> method (1) and therefore have a bigger weighting on the overall % diff in
> method (1).

> The average % difference I am trying to calculate is simply an indicative
> average which best represents a random sample of 'units', so I'm leaning
> towards method (2) because it doesn't include the wieghting in method (1).
> And besides, it is very unlikely that other collections of units would
> include the high value items that are overly weighting the resulting %
> diff in method (1).

What you probably want is the median value not the arithmetic mean
(average).
http://en.wikipedia.org/wiki/Median

Check out the "median" function in Excel help. The "daverage" function might
also accomplish what you want.

Signature

Bob
http://www.kanyak.com

Reply to this Message