Differences in Averages

[Blackhawks]

Hey everyone,

I feel like a complete goof asking this, but why is it when you take the average of a set as a whole it equals A, but when you break that set up into 3 and take the average, it can sometimes equal a different number? In the example it's only off by .52, but I've had it off by 10 or more in some cases.

See the attached file for an example. Also, is there a way to harmonize this?

[Pete_UK]

In your example there are 7 numbers in the first 2 groups, but only 5 in the last group. By taking an average of the 3 averages, you are assuming an equal weight from the groups, but clearly the third group is only contributing 5/19 to the overall average, whereas the other two groups are each contributing 7/19 ths. Make your group sizes the same, or use a weighted average formula.

Hope this helps.

Pete

[FlameRetired]

OK. Pete_UK posted while I was attempting to fashion a more tedious presentation.
What follows is a detailed version of exactly what Pete_UK is saying.

Be advised that explanations are not my long suit.

Though not exactly an “explanation” it can be helpful to reveal what is going on inside the formulas.

Your approach works if you remember how you got to those three averages and re-multiply them by the count of the numbers you were originally averaging and sum them and then divide by the total count of numbers (19).

See D1 and its formula in E1. The results of all the COUNT function are in cell D2 with corresponding formula in E2.
Those results in D1 and D2 are exactly the same as if you had used the formula that is in D3, revealed in E3.

What you have attempted to do is expressed in D4 & E4. You’ve SUMed the AVERAGES and then divided by 3. You can verify this by the results in D4 that are identical to the results you got in F9 of your attached file where you AVERAGED the three values in B3, B10 and B16.

	D	E
1	28.05263	=($B$3*COUNT($A$1:$A$7)+$B$10*COUNT($A$8:$A$14)+$B$16*COUNT($A$15:$A$19))/COUNT($A$1:$A$19)
2	28.05263	=($B$3*7+$B$10*7+$B$16*5)/19
3	28.05263	=SUM($A$1:$A$19)/COUNT($A$1:$A$19)
4	28.10476	=($B$3+$B$10+$B$16)/3
5	28.05263	=AVERAGE($A$1:$A$19)

I hope this is somewhat helpful.
FR

[Blackhawks]

thank you gentlemen, I really appreciate it! It makes perfect sense!

[FlameRetired]

thank you gentlemen, I really appreciate it! It makes perfect sense!

You're welcome.