# Thread: Formula to compute averages with criteria

1. ## 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

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

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

4. 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 sleep-deprived to think straight?

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

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

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

8. Hi Steve

Thanks for the explanation. It makes perfect sense

Regards

Howard

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•