Shapiro-Wilk and other normality tests in Excel

 

Dataset for Example

The data represent two samples each containing the average math score of 1000 students.

The goal of this tutorial is to know whether this dataset follows a normal distribution.

 

Setting Up

• Open XLSTAT.

• Click on the Describing data menu and select the Normality tests feature:

• Once you've clicked on the button, the dialog box appears.

• In the General tab, select the two samples in the Data field.

• In the Charts tab, activate the Q-Q plot option to allow us to visually check the normality of the samples.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

Normality tests are crucial to determine if a dataset follows a normal distribution, which is an assumption in many statistical models. The null hypothesis is that the data comes from a normal distribution, and a low p-value (p < alpha threshold) suggests the data is not normal.

To perform the test, the test statistic WWW is calculated as:

W=(∑i=1naix(i))2∑i=1n(xi−xˉ)2W = \frac{\left( \sum_{i=1}^{n} a_i x_{(i)} \right)^2}{\sum_{i=1}^{n} (x_i - \bar{x})^2}W=∑i=1n​(xi​−xˉ)2(∑i=1n​ai​x(i)​)2​

If WWW falls below the critical value in the Shapiro-Wilk table, the p-value is less than alpha, rejecting the null hypothesis, and indicating the data does not follow a normal distribution.

The results include a Q-Q plot for each sample, comparing the sample's cumulative distribution function (cdf) to a normal distribution's cdf. If the sample follows a normal distribution, it aligns with the bisecting line; otherwise, deviations occur.

The empirical cdf closely aligns with the bisecting line, and both the Shapiro-Wilk and Jarque-Bera tests confirm that we cannot reject the normality assumption. The Shapiro-Wilk test has a smaller risk of error in rejecting the null hypothesis compared to the Jarque-Bera test.

For the second sample, the Q-Q plot shows two strong deviations, suggesting that the distribution is likely not normal, unlike the first sample.

The normality tests confirm the gap, leading us to confidently reject the hypothesis that the sample is normally distributed.

 

Conclusion

In conclusion, the Shapiro-Wilk and Jarque-Bera tests did not reject the normality assumption for the first sample, while they allowed us to reject it for the second sample.