FJCC wrote:Thanks for the clear explanation. It really helps when people take the time to lay out the problem. The attached file shows two ways to weight the average. Neither fixes the basic illegitimacy of such calculations but they do lower the reported number.
I am not sure that I completely follow the solution rationales.
I would have thought the (probably) correct approach would be to the weighted mean for each row, for so for Category A we have
saa = (231*5, 143 * , 45* , 3*2, 1*1) and
san = sum(231, 143, 45, 3, 1)
Weighted mean for A = saa/san
Repeat for Categories B, C, D.
Then for the Grand Mean we take the we take the four sums saa , sbb, scc, sdd and divide by san, + sbn, etc. That is the sum of H2:H5 in your example.
I suspect that the terminology the OP is using is partly to blame. It looks to me that the 5-point scale should be considered a rating scale not a ranking scale. In the social sciences this usually allows one to make the (slightly dubious) assumption of interval data.
Sorry for not supplying a working example but what calculations I did do I did it another program. I do think that the weighted means for each category should be:
A : 4.41844
B : 2.1875
C : 2.914634
D : 3.963277
It seems very strange that Calc does not offer a weighted.mean function.