Using Excel to conduct normality test
Suppose we have these 10 values:
|
5.62 |
7.19 |
14.69 |
17.94 |
23.74 |
31.52 |
31.71 |
35.31 |
39.21 |
39.91 |
1) Type them into column A from A1 through A10.
2) In cell A11 type =average(a1:a10) . (Don’t forget the = sign.) In cell A12 type =stdev(a1:a10) . This gives you the mean and standard deviation of your data.
3) In cell B1 type =(a1-a$11)/a$12 . The $ fixes the row location. Drag and drop B1 through B10. This gives the Z values.
4) In cell C1 type =normsdist(b1) . Drag and drop C1 through C10. This gives F(z).
5) In cells J1 through J10 type the numbers 1 through 10. Then in cell D1 type =j1/10 . Drag and drop through D10. This gives S(z).
6) In cell E1 type =D1-1/10 . Drag and drop through E10. This gives S’(z).
7) In cell F1 type =max(abs(c1-d1),abs(c1-e1)) . Drag and drop through F10. This gives max D.
8) In cell F11 type =max(f1:f10) . This gives the test statistic.
Here is how your spreadsheet should look (accurate to 4 decimals):
|
5.62 |
-1.4929 |
0.0677 |
0.1 |
0 |
0.0677 |
|
7.19 |
-1.3700 |
0.0853 |
0.2 |
0.1 |
0.1147 |
|
14.69 |
-0.7826 |
0.2169 |
0.3 |
0.2 |
0.0831 |
|
17.94 |
-0.5281 |
0.2987 |
0.4 |
0.3 |
0.1013 |
|
23.74 |
-0.0739 |
0.4705 |
0.5 |
0.4 |
0.0705 |
|
31.52 |
0.5353 |
0.7038 |
0.6 |
0.5 |
0.2038 |
|
31.71 |
0.5502 |
0.7089 |
0.7 |
0.6 |
0.1089 |
|
35.31 |
0.8321 |
0.7973 |
0.8 |
0.7 |
0.0973 |
|
39.21 |
1.1375 |
0.8723 |
0.9 |
0.8 |
0.0723 |
|
39.91 |
1.1924 |
0.8834 |
1 |
0.9 |
0.1166 |
|
24.684 |
|
|
|
|
0.2038 |
|
12.7696 |
|
|
|
|
|
You’ll note that the test statistic of 0.2038 is slightly different than that of 0.2054 computed in class. This is because we rounded our Z values to 2 decimals in order to use the Z tables. Of course, there is no such need to do that in Excel.