Results 1 to 8 of 8

20110914, 13:22 #1
 Join Date
 Feb 2008
 Posts
 1,568
 Thanks
 141
 Thanked 12 Times in 12 Posts
Formula to compute averages with criteria
I have a spreadsheet with Average Stocking holdings in Col B as well as an average of the total. The Stock is by branch and is split into two categories NV & UV. This is notes at the end of each branch in Col A for eg BR1NV, BR1UV
I want to use compute the average for each category. I tried using Averageif for NV as well as for UV, but when I total up the Averageifs (Cell B37), it is exactly double to the total of the average in cell B21
It would be appreciated if someone could assist in resolving this issue

20110914, 14:36 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 Posts
What issue? What do you think is incorrect? Since the average of NV and UV should be equal to the overall average, and the average of those two is the sum divided by 2, it stands to reason that the two averages will add up to double the overall average.
Regards,
Rory
Microsoft MVP  Excel

20110914, 14:37 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Why would you expect the avg of 2 halves to equal the avg of the whole?
Your check should include not only the average but the count. The overal Avg = (Avg1*N1 + Avg2*N2) / (N1+N2)... and your check count is that the total count = N1+N2
Steve

20110914, 16:59 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 Posts
Why would the average of two halves not equal the average of the whole? Average of sum(group1)/n and sum(group2)/n must be (group1+group2)/2n where n is half the total, or am I too sleepdeprived to think straight?
Regards,
Rory
Microsoft MVP  Excel

20110914, 21:52 #5
 Join Date
 Feb 2008
 Posts
 1,568
 Thanks
 141
 Thanked 12 Times in 12 Posts
Hi Guys
Thanks for the reply. The sample that I attached computed the average stockholding days for all categories as =AVERAGE(B3:B31)
Are you saying that to compute the average of each category FOR EG NV that I must divide the AVERAGEIF by 2 i.e =AVERAGEIF($A$4:$B$29,"*NV*",$B$4:$B$31)/2 and the same for UV =AVERAGEIF($A$4:$B$29,"*UV*",$B$4:$B$31)/2
Your assistance in this regard will be most appreciated

20110914, 22:20 #6
 Join Date
 Feb 2008
 Posts
 1,568
 Thanks
 141
 Thanked 12 Times in 12 Posts
Hi Guys
I was half asleep when I relooked at the problem.
The formula =AVERAGEIF($A$4:$B$29,"*NV*",$B$4:$B$31) for each category is correct the total of the 2 AVERAGES would be the total of the 2 categories divided by 2. Don't know why I never thought of this in the first instance
Regards
Howard

20110915, 08:03 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
That is only true if the subgroups always have the same number of entries. If the subgroups do not have the same number of entries the sum/2 will not be the overall average.
The overall avg = (Avg1*N1 + Avg2*N2) / (N1+N2) ONLY if N1=N2 will Overall Avg = (Avg1+Avg2)/2
Steve

20110915, 10:15 #8
 Join Date
 Feb 2008
 Posts
 1,568
 Thanks
 141
 Thanked 12 Times in 12 Posts
Hi Steve
Thanks for the explanation. It makes perfect sense
Regards
Howard