Average is not Average

[scantor145]

Please see attached spreadsheet.

I have two columns of values.

Column #1 contains an outlier so it is marked with an asterisk. Therefore it should not be used in the calculations.

The average of Column #1 is 138.22.
The average of Column #2 is 138.97.

If I average both columns in one formula I get 138.63.
If I calculate the average of the averages I get 138.59.

Why is there a difference?

[AlKey]

Doing average over average is never a right solution and it is just a bad practice.

[scantor145]

That may be true, but it doesn't explain the issue.

[MrShorty]

Perhaps I am misunderstanding, but it is a fairly well known property of averages that the average of a set of numbers is not the same as the average of two subsets of the set of numbers. You should be able to see this from the definition of average as applied to both scenarios.

Average of the whole set =[sum(a_values)+sum(b_values)]/[count(a_values)+count(b_values)]
average of the two averages =[sum(a_values)/count(a_values)+sum(b_values)/count(b_values)]/2

I expect that there are specific situations where the two averages will be the same [such as when count(a_values)=count(b_values)], but, in general, the two averages are not expected to be the same.

Which method of calculating the overall average depends on exactly what you are doing. When in doubt, they usually say that the first approach is more correct, but you need to know enough about what you are doing to make that final determination.

[AliGW]

This is correct:

=AVERAGE(C4:C9,F4:F9)

It's the sum of all values in the ranges excluding the one with the asterisk divided by 11 - do the calculation manually and you will see.

The other calculation is:

=AVERAGE(C10,F10)

This is the sum of C10 and F10 divided by 2. This is not correct, as it weights the two sums equally.

C4:C9 has only five valid numbers - its average is the sum divided by FIVE.

F4:F9 has six valid numbers - its average is the sum divided by SIX.

This means that the average of all valid numbers in the two renages is their sum divided by ELEVEN.

[Zer0Cool]

Average of a set and average of averages is not the same thing, the only time they are equal is by fluke/chance (generally seen in small sample sets/examples.) or when each subset are evenly divisible by the total elements of the set (because 100/4 = 50/2).

So for example a data set like: 100, 100, 50, 100 would have the same average for the whole set as the average of the average of A and B & C and D.

IE: 100 + 100 = 200 /2 = 100
50 + 100 = 150 /2 = 75
100 + 75 = 175 /2 = 87.5
or
100 + 100 + 50 + 100 = 350 /4 = (350/2)/(4/2) = 175/2 = 87.5

But the set: 100, 100, 50, 100, 100 will not have the same average of averages when the grouping is 2/3.

IE: A and B then C, D and E
100 +100 = 200 /2 = 100
50 + 100 + 100 = 250 /3 = 83.3333
100 + 83.3333 = 183.3333/2 = 91.6666666666667
but
100 + 100 + 50 + 100 + 100 = 450 /5 = 90

90 <> 91.6666666666667

[AliGW]

Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.