average formulas discrepancy

Question

I need help in figuring out why the formulas return different values. I calculated average price of unit sold showing in the 4 column using formula =C2/B2. Then I calculated average of all average unit prices shown on the bottom of column D using =AVERAGE(D2:D8). At the bottom of column E however I used =C9/B9 which returned much higher value than in D9. Why are D9 and E9 values different?

A	B	C	D	E
	12	24,282	2,023.50
	30	35,885	1,196.17
	61	115,867	1,899.46
	60	113,933	1,898.88
	36	89,520	2,486.67
	34	46,441	1,365.91
	8	9,994	1,249.25
TOTAL	241	435,922	1,731.41	1,808.80

Answer 1

In general, the average of averages is not the same as the average of the whole.

The exception is when each average has the same divisor; that is, the same number in column B.

The correct formula in D9 is the weighted average:

=SUMPRODUCT(D2:D8, B2:B8) / SUM(B2:B8)

Of course, you can also use a formula similar to the operation in E9:

=SUM(C2:C8) / SUM(B2:B8)

The latter is better, when you have all of the data -- C2:C8 and B2:B8.

Note that SUMPRODUCT(D2:D8, B2:B8) is algebraically the same as SUM(C2:C8). For example, since D2 = C2/B2, D2*B2 = B2*C2/B2 = C2.

Answer 2

Thanks it worked fine