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. 


Thread Tools  Display Modes 
#1




"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




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




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




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  


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