Weighted Average?

[Jay]

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

 

[Stan Brown]

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

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

 

[Opinicus]

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.