Shapiro-Wilk and other normality tests in Excel tutorial

This tutorial explains how to perform and interpret Shapiro-Wilk and other normality tests in Excel using XLSTAT.

Dataset for Shapiro-Wilk and other normality tests

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

Goal of this tutorial

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

Setting up a Shapiro-Wilk and other normality tests

Once XLSTAT is open, click on Describing data / Normality tests.
Once you've clicked on the button, the dialog box appears.
Select the two samples in the Data field.

In the Charts tab, the Q-Q plot option is activated to allow us to visually check the normality of the samples.
The computations begin once you have clicked on the OK button, and the results are displayed on a new sheet.

Interpreting the results of Shapiro-Wilk and other normality tests

It would help to remember, why we need to run a normality test. Normality tests enable you to know whether your dataset follows a normal distribution. Moreover, the normality of residuals is a required assumption in common statistical modeling methods. Normality tests involve the null hypothesis that the variable from which the sample is drawn follows a normal distribution. Thus, a low p-value indicates a low risk of being wrong when stating that the data are not normal. In other words, if p-value < alpha risk threshold, the data are significantly not normal. And, how do normality tests work?

We calculate the test statistic below on our dataset :

W=\dfrac{(\sum_{i=1}^na_ix_{(i)})^2}{\sum_{i=1}^n(x_i-\bar{x})^2}

If its values are below the bounds defined in the Shapiro-Wilk table for a set alpha threshold, then the associated p-value is less than alpha and the null hypothesis is rejected and the data does not follow a normal distribution.

The results are first displayed for the first sample and then for the second sample.

The first result displayed is the Q-Q plot for the first sample. The Q-Q plot allows us to compare the cumulative distribution function (cdf) of the sample (abscissa) to the cumulative distribution function of the normal distribution with the same mean and standard deviation (ordinates). In the case of a sample following a normal distribution, we should observe an alignment with the first bisecting line. In the other cases, some deviations from the bisecting line should be observed.

We can see here that the empiric cdf is very close to the bisecting line. The Shapiro-Wilk and Jarque-Bera confirm that we cannot reject the normality assumption for the sample. We notice that with the Shapiro-Wilk test, the risk of being wrong when rejecting the null assumption is smaller than with the Jarque-Bera test.

The following results are for the second sample. Contrary to what we have observed for the first sample, we notice here on the Q-Q plot that there are two strong deviations indicating that the distribution is most probably not normal.

This gap is confirmed by the normality tests (see below) which allow us to assert with no hesitation that we need to reject the hypothesis that the sample might be normally distributed.

Conclusion

In conclusion, in this tutorial, we have seen how to test two samples for normality using Shapiro-Wilk and Jarque-Bera tests. These tests did not reject the normality assumption for the first sample and allowed us to reject it for the second sample.

Was this article useful?