878
Comment:
|
1341
converted to 1.6 markup
|
Deletions are marked like this. | Additions are marked like this. |
Line 1: | Line 1: |
= How do I compute Mean Square Error (MSE) in EXCEL or SPSS? = | |
Line 2: | Line 3: |
= How do I compute Mean Square Error (MSE)? = Mean square error or MSE is frequently requested by journals as a companion statistic for ANOVA and, especially, t-tests. It is the average intra-group variance. MSE is outputted in ANOVA tables but can be computed using a weighted average of the group variances. |
Mean square error or MSE is frequently requested by journals as a companion statistic for ANOVA and, especially, t-tests. MSE is the average intra-group variance. MSE is outputted in ANOVA tables but can be computed using a weighted average of the group variances. |
Line 8: | Line 7: |
MSE = $$\frac{\sum_text{i} (N_text{i}-1) SD^text{2}_{i}}{\sum_text{i} (N_text{i}-G) } $$ | MSE = $$\frac{\sum_text{i} (N_text{i}-1) SD_text{i}^text{2}}{(\sum_text{i}N_text{i})-G } $$ |
Line 10: | Line 9: |
which equals $$\frac{\sum_text{i} SD^text{2}_{i}}{G} $$ in the sepcial case of equal group sizes. | which equals $$\frac{\sum_text{i} SD_text{i}^text{2}}{G} $$ in the special case of equal group sizes. |
Line 12: | Line 11: |
In EXCEL, for two groups (A and B) in cells A1:A100 and B1:B100 respectively this corresponds to | MSE is also useful for computing Cohen's d effect size, the number of standard deviations between a pair of group means, because |
Line 14: | Line 13: |
MSE =(SQRT(((COUNT(A1:A100)-1)+(COUNT(B1:B100)-1)))*(AVERAGE(A1:A100)-AVERAGE(B1:B100)))/(SQRT((COUNT(A1:A100)-1)*POWER(STDEV(A1:A100),2)+(COUNT(B1:B100)-1)*POWER(STDEV(B1:B100),2))) | d = (difference in a pair of group means) divided by the square root of the MSE In EXCEL, suppose we have two groups (A and B ) in cells A1:A100 and B1:B100 respectively, Cohen's d can then be computed using COHENSD =(SQRT(((COUNT(A1:A100)-1)+(COUNT(B1:B100)-1)))*(AVERAGE(A1:A100)-AVERAGE(B1:B100)))/(SQRT((COUNT(A1:A100)-1)*POWER(STDEV(A1:A100),2)+(COUNT(B1:B100)-1)*POWER(STDEV(B1:B100),2))) and the mean square error in EXCEL is computed by MSE = ((COUNT(A1:A100)-1)*POWER(STDEV(A1:A100),2)+(COUNT(B1:B100)-1)*POWER(STDEV(B1:B100),2))/((COUNT(A1:A100)-1)+(COUNT(B1:B100)-1)) |
How do I compute Mean Square Error (MSE) in EXCEL or SPSS?
Mean square error or MSE is frequently requested by journals as a companion statistic for ANOVA and, especially, t-tests. MSE is the average intra-group variance. MSE is outputted in ANOVA tables but can be computed using a weighted average of the group variances.
Fo G groups with the i-th an SD, $$SD_text{i}$$ and sample size $$N_text{i}$$,
MSE = $$\frac{\sum_text{i} (N_text{i}-1) SD_text{i}^text{2}}{(\sum_text{i}N_text{i})-G } $$
which equals $$\frac{\sum_text{i} SD_text{i}^text{2}}{G} $$ in the special case of equal group sizes.
MSE is also useful for computing Cohen's d effect size, the number of standard deviations between a pair of group means, because
d = (difference in a pair of group means) divided by the square root of the MSE
In EXCEL, suppose we have two groups (A and B ) in cells A1:A100 and B1:B100 respectively, Cohen's d can then be computed using
COHENSD =(SQRT(((COUNT(A1:A100)-1)+(COUNT(B1:B100)-1)))*(AVERAGE(A1:A100)-AVERAGE(B1:B100)))/(SQRT((COUNT(A1:A100)-1)*POWER(STDEV(A1:A100),2)+(COUNT(B1:B100)-1)*POWER(STDEV(B1:B100),2)))
and the mean square error in EXCEL is computed by
MSE = ((COUNT(A1:A100)-1)*POWER(STDEV(A1:A100),2)+(COUNT(B1:B100)-1)*POWER(STDEV(B1:B100),2))/((COUNT(A1:A100)-1)+(COUNT(B1:B100)-1))