MS Excel - Example No. 9
A) Statistical Evaluation of Differences between Preparations:
1. Input data of samples into a table (cells B3:B12 - E3:E12); complete the table according to the model:
2. Calculation of mean value (average) of samples: in cells B13 - E13 call menu Insert - Function - Statistical - AVERAGE:
3. Calculation of standard deviation (SD) for sample data: in cells B14 - E14 call menu Insert - Function - Statistical - STDEV:
4. Into field Array 1 in dialog window input cells for calculation - e.g. B3:B12 (it is possible to mark them by means of mouse), push the button OK to get result:
5. Testing for difference between variances by means of F-test: in the cell C16 (then also D16, E16) call menu Insert - Function - Statistical - FTEST:
6. Result of F-test: P=0.24059 indicates the probability of null hypothesis about homogeneity of variances (= probability of a error). As calculated P > 0.05, it means that difference between variances is statistically insignificant (H0: s1=s2 is true).
7. Calculation of unpaired t-test for difference between mean values of statistical set: in the cell C17 (then also D17, E17) call menu Insert - Function - Statistical - TTEST:
Into field Array 1 in dialog window input cells for calculation - B3:B12 (Control sample), into field Array 2 input cells C3:C12 (Preparation A) - it is possible to mark them by means of mouse, in Tails: 2, in Type: 2 (Two-sample equal variance - according to the result of F-test); push the button OK to get result:
8. Results of t-tests: values in cells C17-E17 indicates probability of null hypotheses about equality of mean values of control and test sets - prep. A, B ,C (= probability of a error). It means:
Preparation A (P=0.70985): statistically insignificant difference between means
Preparation B (P=0.00685): statistically highly significant difference between means
Preparation C (P=0.042101): statistically significant difference between means
9. Conclusion: Preparation A does not influence (P>0.05) the Mg level in blood serum of dairycows, preparation B has the statistically highly significant effect (P<0.01) and preparation C has the significant effect (P<0.05) on the Mg level in blood serum of dairycows.
Note:
t-test in this example used gives only aproximate results. For more detailed testing for differences among several mean values other special methods are designated - so called multiple comparison tests that should follow analysis of variance (ANOVA), which rejected global null hypothesis m1=m2=m3....=mm. These multiple comparison tests treat unintentional increasing of error a (which occurs always in the process of comparison of several means) in different ways: for maintenance of the level 0.05 in the whole experiment, which compares all possible pairs of monitored groups, it is necessary to performe each separate test at the level a / number of tests. In general we can say that the significances of differences found between pairs of groups in multiple comparison tests is lower than by using of the typical two-sample t-tests. For solving of this model example e.g. the Dunnett test should be typically suitable (comparison of several test samples to one control sample), which gives more precise results in this case: A x Contr. (P=0.9705), B x Contr. (P=0,0020), C x Contr. (P=0,1227). However, use of ANOVA method and following multiple comparison tests requires a specific statistical software (UNISTAT, SPSS ...).
B) Graphical Presentation of Data:
1. Mark 2 blocks of cells B2:E2 a B13:E13 (discontinous blocks - by means of Ctrl) and call menu Chart Wizard:
2. Option Column Chart (as the chart type), a preview is displayed in dialog window 2/4:
3. Input Chart Title, Axes X and Y description (3/4 dialog window):
4. Select a position of the chart (As object in: default sheet):
5. Complete the chart through button Finish. It is possible to call further options of the chart after selecting various objects in the chart by means of context menu (right button of the mouse or menu bar):
