"Average" function query
 

Hi all,

I have an Excel function query that's really bugging me. In column "A" I
have about 80 staff annual salaries. In column "B" I have their
corresponding annual salary increase.

If I sum Column "A", and sum Column "B", and divide the resultant totals, I
get what I assume to be the average annual salary increase for the group
(3.46%).

However, if in Column "C" I calculate each individual's average annual
increase (B2/A2 etc), and then use the AVERAGE function over all of the
results in column "C" I get a different answer (3.79%).

Why is this please, and which answer is correct?

Thanks,

Robin

I believe you need to do a weighted average of the %s, not an average. To do
that use

=SUMPRODUCT(A2:A81,C2:C81)/SUM(A2:A81)

you should come up with the 3.46% figure.




"Robin Blackwell" wrote:

Hi all,

I have an Excel function query that's really bugging me. In column "A" I
have about 80 staff annual salaries. In column "B" I have their
corresponding annual salary increase.

If I sum Column "A", and sum Column "B", and divide the resultant totals, I
get what I assume to be the average annual salary increase for the group
(3.46%).

However, if in Column "C" I calculate each individual's average annual
increase (B2/A2 etc), and then use the AVERAGE function over all of the
results in column "C" I get a different answer (3.79%).

Why is this please, and which answer is correct?

Thanks,

Robin

It is because the denominators (the salaries in column A) of the fractions
being added (when you average the percent column) aren't all the same.
Remember back to fraction addition:

7/4 + 8/2 DOES NOT equal 15/6... but instead 8/2 = 16/4, and so 7/4+16/4 =
23/4.

--
Regards,
Dave


"Robin Blackwell" wrote:

Hi all,

I have an Excel function query that's really bugging me. In column "A" I
have about 80 staff annual salaries. In column "B" I have their
corresponding annual salary increase.

If I sum Column "A", and sum Column "B", and divide the resultant totals, I
get what I assume to be the average annual salary increase for the group
(3.46%).

However, if in Column "C" I calculate each individual's average annual
increase (B2/A2 etc), and then use the AVERAGE function over all of the
results in column "C" I get a different answer (3.79%).

Why is this please, and which answer is correct?

Thanks,

Robin

Robin Blackwell wrote:
In column "A" I have about 80 staff annual salaries.
In column "B" I have their corresponding annual salary
increase.

I assume these are in A2:A81 and B2:B81 respectively.

If I sum Column "A", and sum Column "B", and divide the
resultant totals, I get what I assume to be the average
annual salary increase for the group (3.46%).

However, if in Column "C" I calculate each individual's
average annual increase (B2/A2 etc), and then use the
AVERAGE function over all of the results in column "C"
I get a different answer (3.79%).

Why is this please, and which answer is correct?

The answer is: both(!). It depends on what you want to
compute.

The first value, SUM(B2:B81)/SUM(A2:A81), is the percentage
increase of the total salary cost for the group. (It is
not really an average.)

The second value, SUMPRODUCT(B2:B81/A2:A81)/COUNT(A2:A81),
is the average salary increase per individual.

(Oddly, SUMPRODUCT(...)/COUNT(...) is equivalent to the
way you computed the average. There might be a better
formula that is more apparent to the reader.)

The reason for the difference is because
(b2+...+b81)/(a2+...+a81) does not equal
b2/a2 +...+ b81/a81, much less (b2/a2 +...+ b81/a81)/80.

For example, consider A2:A3 = {2,3} and B2:B3 = {1,1}.
(1/2 + 1/3)/2 equals (5/6)/2 (5/12), and (1+1)/(2+3)
equals 2/5.