Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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 sleep-deprived to think straight?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 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

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    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
  •