A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Average" function query



 
 
Thread Tools Display Modes
  #1  
Old October 3rd, 2005, 07:35 PM
Robin Blackwell
external usenet poster
 
Posts: n/a
Default "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


Ads
  #2  
Old October 3rd, 2005, 07:51 PM
Duke Carey
external usenet poster
 
Posts: n/a
Default

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



  #3  
Old October 3rd, 2005, 08:05 PM
David Billigmeier
external usenet poster
 
Posts: n/a
Default

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



  #4  
Old October 3rd, 2005, 09:22 PM
external usenet poster
 
Posts: n/a
Default

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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
function error in query (unknown function name) Bruce L Running & Setting Up Queries 5 October 3rd, 2005 09:22 PM
Toolbars, Drop-Down Menus Rick New Users 1 September 21st, 2005 11:17 AM
Attaching Code DS General Discussion 2 August 22nd, 2005 11:21 PM
Append Query - Increment Number with Function? [email protected] Running & Setting Up Queries 3 May 2nd, 2005 05:15 PM
I want to use the MATCH function with the AVERAGE function but I . Miguel Worksheet Functions 2 April 23rd, 2005 05:29 PM


All times are GMT +1. The time now is 04:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.