Problem with Averages

[garfas]

Hi All,

I'm working on this problem about test scores and I'm trying to figure out why I'm getting a different number when I get a mean of pupil grade score percentages versus when I do the other way by getting a mean for each individual test and then averaging that? I have noticed that if the the number of questions in each test are equal, I don't have this problem (see example 2) Any help greatly appreciated? Gar



EXAMPLE1

		Test1	Test2	Test3
	Out of	12	20	10	TOTAL	Out of	Pupil Avg %
	Pupil1	12	10	9	31	42	73.809523810%
	Pupil2	8	12	7	27	42	64.285714286%
	Pupil3	12	14	8	34	42	80.952380952%
	Pupil4	12	14	9	35	42	83.333333333%
	Pupil5	8	8	8	24	42	57.142857143%
	Pupil6	12	13	9	34	42	80.952380952%
Test	Avg	10.66666667	11.83333333	8.3333333	30.83333		73.4126984%		Mean Pupil Avg %
Test	Avg %	0.888888889	0.591666667	0.83333333
Mean	Test Avg %	77.1296296%		Why are these Averages different?
EXAMPLE2
		Test1	Test2	Test3
	Out of	14	14	14	TOTAL	Out of	Pupil Avg %
	Pupil1	12	10	9	31	42	73.809523810%
	Pupil2	8	12	7	27	42	64.285714286%
	Pupil3	12	14	8	34	42	80.952380952%
	Pupil4	12	14	9	35	42	83.333333333%
	Pupil5	8	8	8	24	42	57.142857143%
	Pupil6	12	13	9	34	42	80.952380952%
Test	Avg	10.66666667	11.83333333	8.3333333	30.83333		73.4126984%		Mean Pupil Avg %
Test	Avg %	0.761904762	0.845238095	0.5952381
Mean	Test Avg %	73.4126984%		Ok when test question numbers equal

<colgroup><col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:4461;width:92pt" width="122"> <col style="mso-width-source:userset;mso-width-alt:1170;width:24pt" width="32"> <col style="width:48pt" width="64" span="3"> </colgroup><tbody>
</tbody>

 

[jmacleary]

Hi there.

If youre worried that excel is getting it wrong, dont be. The calculations come out differently because of what it is that you are averaging. Averaging averages can and does reult in this kind of apparent anomaly.

 

[garfas]

Ok. I've been doing some research on this. Now I'm wondering, as the test question number is different, if I should be using weighted averages using SUMPRODUCT