Your data analysis solution

Distribution sampling & normality tests in Excel

2017-10-20

In this tutorial we will 1) generate a sample from a Normal distribution and one from a uniform distribution, and 2) we will perform normality tests on both samples, in Excel using XLSTAT.

Dataset for sampling a distribution and running a Normality test

An Excel sheet with both the data and the results can be downloaded by clicking on the button below:
Download the data

We want to generate two samples, the first one sampled in a Normal N(2,2) distribution (mean = 2, standard deviation (sigma) = 2), the second one in a Uniform distribution, between -1.5 and 5. To do that, we use the "Distribution sampling" tool available in the "Preparing data" section.

Setting up the sampling of data in a distribution

After opening XLSTAT, select the XLSTAT / Preparing data / Distribution sampling command, or click on the corresponding button of the Preparing data menu (see below).
XLSTAT Function for Distribution Sampling

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

Select the distribution to use, and the corresponding parameters.

Then enter the size of the sample to generate.

The dialog box displayed below corresponds to the generation of a 1000 cases normal N(2,2) sample.

XLSTAT dialog box for distribution sampling (normal)

The computations begin once you have clicked on the OK button, and the sample is displayed.

A second sample is then generated using a Uniform distribution between -1.5 and 5. To generate that sample next to the other one, deactivate the option "Display the report header" and select the range next to the first sample name.

XLSTAT dialog box for distribution sampling (uniform)

Click on Ok and the second sample is displayed.

Setting up the Normality test

We then want to tests the normality of the two samples. Select the XLSTAT / Describing data / Normality tests, or click on the corresponding button of the Describing data menu.

XLSTAT function for normality tests

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

Select the two samples in the Data field. 

The Q-Q plot option is activated to allow us to visually check the normality of the samples.

XLSTAT dialog box for normality tests (general tab)

XLSTAT dialog box for normality tests (charts tab)

The computations begin once you have clicked on the OK button, and the results are displayed on a new sheet.

Interpreting the results of the Normality test

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 to compare the cumulative distribution function (cdf) of the sample (abscissa) to the cumulative distribution function of normal distribution with the same mean and standard deviation (ordinates). In the case of 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.

Q-Q plot (Normal)

We can see here that the empiric cdf is very close to the bissecting 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.

Results for Shapiro-Wilk and Jarque_Berra tests

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

Q-Q plot (Uniform)

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

Results for Shapiro-Wilk and Jarque_Berra tests

Conclusion

As a conclusion, in this tutorial we have seen how to generate two samples, one following a Normal distribution, the second following a Uniform distribution. We then applied the Jarque-Bera and the Shapiro-Wilk tests on them. These tests did not reject the normality assumption for the first sample, and allowed us to reject it for the second sample.

1c26995d494fb3061dd0ae8571ffc0a4@xlstat.desk-mail.com
https://cdn.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/portal/articles/autocomplete
9283